Functions vs Stored Procedures

sql sql-server database function stored-procedures
Auron picture Auron · Oct 7, 2008 · Viewed 41.1k times · Source

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use?

In short, what I want to know is:

Which are the main differences between functions and stored procedures? What considerations do I have to take into account for using one or the other?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Oct 7, 2008

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.

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More