Stored procedures vs. functions

SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:

• Reusing code from one program to another, cutting down on program development time
• Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages
• Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications

At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:

• 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.

Overall, stored procedures are one of the greatest treasures available to SQL Server developers. I use them heavily in my databases and encourage you to do the same. The efficiency and security benefits you’ll reap are well worth the upfront investment in time.