Functions vs Stored Procedures Functions vs Stored Procedures database database

Functions vs Stored Procedures


If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.

Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.

So use whichever one minimally allows you to express your desired result.


Functions must be deterministic, and cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc.

You should limit your use of functions, since they pose a huge scalability problem for big, complex queries. They become sort of a "black box" for the query optimizer, and you'll see enormous differences in performance between using functions and simply inserting the code into a query.

But they are definitely useful for table-valued returns in very specific cases.

If you need to parse a comma-delimited list, to simulate passing an array to a procedure, a function can turn the list into a table for you. This is common practice with Sql Server 2005, since we can't pass in tables to stored procedures yet (we can with 2008).


From the docs:

If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:

  • The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters.

  • The stored procedure does not perform update operations, except to table variables.

  • There is no need for dynamic EXECUTE statements.

  • The stored procedure returns one result set.

  • The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement.