Stored Procedures vs. User-Defined Functions in Microsoft SQL Server

Writing inline user-defined functions in SQL Server requires simpler coding

Senior woman using laptop, focus on hands
Julian Ward / Getty Images

SQL Server user-defined functions and stored procedures offer similar functionality to developers. 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 because you can save programming time by:

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

Stored Procedures vs. User-Defined Functions

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. User-defined functions usually involve less coding and contain simpler code that is easier to maintain that stored procedure code.
  • User-defined functions are simpler to invoke that stored procedures. However, stored procedures deliver better performance and higher productivity.
  • Stored procedures avoid redundant coding that may exist with user-defined functions.
  • Stored procedures 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, who use them heavily in databases. The efficiency and security benefits you reap are well worth the upfront investment in time.