Stored Procedures Vs User Defined Functions

• Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
• Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement_item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
• Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
• UDFs can't change the server environment or your operating system environment, while a SPROC can.
  
• Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support).
  
• You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.