T-SQL Tuesday #24: Functions v. Procedures

calendar Nov 8, 2011 · SQL Server TSQL Tuesday  ·
Share on: linkedin copy

T-SQL Tuesday Logo This month brings us the 24th edition of T-SQL Tuesday (24 already? wow….). This time around our topic comes from Brad Schulz (blog) and has to do with stored procedures and functions. Brad's asking us to write whatever we'd like about either of them.

While from the bird's-eye view both functions (the table-valued variety) and stored procedures appear as packages of code that can be encapsulated, they're completely different and much thought should be given before making the decision to deploy code as either a stored procedure or function. While they are totally different animals, I feel there's a few basic pros and cons of each that should be known:

Stored Procedures

Pros

  • The concept of a stored procedure is pretty well-known and similar across most platforms (you can find stored procedures in Oracle, MySQL, PostgreSQL, etc.)
  • Can perform pretty much any task, queries, administrative, etc.

Cons

  • It's a huge pain to incorporate the result sets from a stored procedure into another query – Table-Valued Functions are much better for this purpose.

Table-Valued Functions

Pros

  • Can be used in a query just like any other table or view, and are very handy for this purpose.
  • Can be thought of as a view that accepts parameters. An excellent choice if you want to expose views to people and shelter them from having to write additional SQL such as WHERE clauses.

Cons

  • Functions can't make changes to the database state, such as changing the recovery model. You can't even create a function that does this – attempting to do so will return error 443.
  • You must list out ALL parameters, even those you just want to use the default value for (and assign them a value of DEFAULT). This behavior is different from a stored procedure, where you can simply omit the parameters that you wish to use a default value for.

I should add that this listing is only the tip of the iceberg. There are many more differences between stored procedures and table-valued functions, and other types of functions (eg. Scalar) exist as well. This is just a quick set of things I keep in my head for general design considerations.