Stored procs vs user defined functions

Kathi Kellenberger, 2007-05-19

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads