Stored procedures vs. functions
In many instances you can accomplish the same task using either a stored procedure or a
function. Both functions and stored procedures can be custom defined and part of any application.
Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For
example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action
query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using
CREATE FUNCTION.
To decide between using one of the two, keep in mind the fundamental difference between them:
stored procedures are designed to return its output to the application. A UDF returns table
variables, while a SPROC can't return a table variable although it can create a table. Another
significant
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in March 2005
difference between them is that UDFs can't change the server environment or your
operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the
function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in
your code (provided you've included error handling support). You'll also find that although a SPROC
can be used in an XML FOR clause, a UDF cannot be.
If you have an operation such as a query with a FROM clause that requires a rowset be drawn from
a table or set of tables, then a function will be your appropriate choice. However, when you want
to use that same rowset in your application the better choice would be a stored procedure.
There's quite a bit of debate about the performance benefits of UDFs vs. SPROCs. You might be
tempted to believe that stored procedures add more overhead to your server than a UDF. Depending
upon how your write your code and the type of data you're processing, this might not be the case.
It's always a good idea to text your data in important or time-consuming operations by trying both
types of methods on them.
Barrie Sosinsky is president of consulting
company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer
topics. His company specializes in custom software (database and Web related), training and
technical documentation.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation