I was at DevTeach/SQLTeach in Montreal last week. The conference was very well run and the organizers did a great job. I gave two presentations, one of which was "T-SQL for Beginners".

During the presentation, a member of the audience asked me a question that I never really thought about before: “What is the difference between a stored procedure and a user defined function (UDF)?” To me this was like asking what the difference is between blue and green – there are many similarities between the two and often they are interchangeable. But, they are different, and I always know which one I need to use.

I mentioned that they are both ways to save T-SQL code within a database. A stored procedure is usually used by an application to return a set of rows, but not always. A UDF is always used to return a value or a table object. But, you can also get values back from a stored procedure by the return code (integer only) or an output parameter. A scalar-valued UDF can be used in a select statement in the select list, where clause and order by clause. You can save the rows returned by a stored proc into a table, but you can also do this with a table-valued UDF. One difference I didn’t think of at the time is that data can not be modified within a UDF.

There are many things that could be done in either a stored procedure or a UDF. For example, in Reporting Services you can set up a multi-valued parameter. This parameter is sent to SQL Server as a comma-delimited string. So, my stored procedure used to return data to the report accepts that parameter. Inside the stored proc, I call a UDF that changes the comma-delimited string into a table object. I save the table object into a temp table or table variable. Now that I have that table I can use it to join to the rest of the tables in my query inside the proc and the results are filtered appropriately.

I could have used a stored proc instead of the UDF to change the string into a table. In this case, the syntax to use each type of object is different, but the functionality is interchangeable. But, I could not use a function in place of the stored procedure that returns the rows to the report. So, there is a difference.

I know that the person asking the question the other day was not satisfied with my answer, and I would have loved to have spent some more time talking with her about it. Unfortunately, it was really off the topic, and I didn’t have a succinct explanation at the tip of my tongue. It would be really interesting to me to know what others would have said if they were asked the same question and had to provide an answer in a sentence or two.