A while back, I was building the database schema for a web application which had some reporting functionality and among other things, I had do implement logic in the database to prepare the data for the application's reports. The queries I constructed were relatively complex which meant that I needed to construct objects in the database to encapsulate these queries. So, it came down to a choice between table-valued functions and stored procedures. If you do a little research you'll notice that there is no clear cut suggestion regarding the choice between functions or stored procedures for cases when you need to retrieve a result set from the database. So, here is a comparison of the two, which, in the end will be concluded with a suggestion for those of you out there who can't make up your minds.
T-SQL statements
With regards to the types T-SQL statements that each of the objects can contain, stored procedures are much more versatile because almost all T-SQL statements can be included in a stored procedure. The only exceptions are the following:
- USE <database>
- CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE, or VIEW
However, when it comes to table-valued functions, there is an entirely different story. Based on the T-SQL statements that can be used in them, they are quite limited. Namely, they cannot:
- Apply schema or data changes in the database
- Change the state of the database or a SQL Server instance
- Create or access temporary tables
- Call stored procedures
- Execute dynamic SQL
- Produce side effects such as relying on the information from a previous invocation.
So, basically, only SELECT statements are allowed in table-valued functions. The only exception is on multistatement-table valued functions, which must contain an INSERT ... SELECT statement that populates the table variable which will be returned by the multi-statement table-valued function.
Parameters and return types
Both, stored procedures and table-valued functions accept parameters of all data types however, there are a few differences.
The first, and most important is that unlike stored procedures, table-valued functions do not accept output parameters. In fact, table-value functions return data to the client in only one way: through the RETURN statement. Stored procedures on the other hand, do accept output parameters and they have three ways to return data to the client: through output parameters, by executing a select statement in the procedure's body or by using a RETURN statement.
Another, more subtle difference is on how parameters with default values are handled. While both stored procedures and table valued functions support default values for parameters, these type of parameters are optional only on stored procedures. Weird as it is, if you want the default value for a parameter when using the function, you have to write the DEFAULT keyword in place of the value for that parameter. With stored procedures you can simply omit the value and SQL Server will supply the default value.
The last difference is that when you call a stored procedure, you can specify the parameter values by association, meaning that you can use a syntax like this: <parameter_name> = <value> to supply the parameter values, which greatly improves the code's readability. You can't do this with functions. This might become an issue if the function has a lot of parameters, because you would constantly need to review the documentation just to find out the order of the parameters in the definition.
So, if you need to return multiple result sets or if you are worried about the readability of your code, stored procedures might be a better option
Performance
If you think about it, table-valued functions, especially inline-table valued functions, are a lot like another database object. Yeap, you guessed it, VIEWS. Even the SQL Server optimizer treats inline table valued functions the same as it does views. This is why one can think of table valued functions like parameterized views.
Performance wise, functions and stored procedures are identical. They both make use of execution plan caching, which means that they are not recompiled every time they are executed. To prove they are identical, you can create a function and a procedure with the same SELECT statement, execute each one a few times, and then check the sys.dm_exec_query_stats DMO. You will notice that the last_elapsed_times differ very little.
Usage
This is where I think, table-valued functions have the greatest advantage. Because they resemble views, they can be placed anywhere a table can be placed in a query. This means that you can filter the result set of the function, use them in join statements, etc. You cannot do the same with stored procedures. Of course, if you have enough knowledge and experience with T-SQL you could probably find some workaround, but generally, manipulating the result set returned by a stored procedure is not as straightforward as doing the same for a table-valued function. So, if for some reason, you need to apply some additional manipulation to the data returned by a function you can do that very easily. If that same data comes from a stored procedure, in most cases you may need to alter the procedure's code, which will require having the necessary permissions and what not.
If you think that the result set of the stored procedure of function may need to be further manipulated, use table valued functions.
One last thing
One thing that I really like about table-valued functions, is that you can use the SCHEMABINDING option on them to prevent any changes on the underlying objects that can break the function. The same option is not always available on stored procedures. You can use it only on natively compiled stored procedures which are available only on SQL Server 2014 and up, and Azure SQL database. So, if you are using regular stored procedures to retrieve data, keep in mind that they can break if you change the structure of the referenced objects.
To conclude, as a general rule of thumb, I tend to use table-valued functions whenever I need to retrieve a result set from the database, and stored procedures when I need to perform some work on the database.