Deciding when to use functions or stored procedures depends on the caller and required work. I want examples behind that choice.
Readers had responded to my SELECT-versus-COUNT and statistics puzzles. I then asked how they choose between functions and procedures. Both organize reusable logic. Reuse alone doesn’t distinguish them.
The original challenge
Share the use case, caller, required result, side effects, and performance evidence behind your choice. Explain the tradeoff when you replace one with the other. A broad slogan is less useful than an example that another reader can check.
Correct the historical Denali note
I previously overstated the SQL Server Denali behavior. WITH RESULT SETS describes returned procedure results through EXECUTE. It doesn’t turn a stored procedure into a SELECT expression or table-valued function.
Keep that distinction when sharing your example. Explain the caller, required results and any side effects. I’m leaving the reader challenge open for your reasoning.
Reference: EXECUTE and WITH RESULT SETS.
Related reading
- SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)
- SQL SERVER – Puzzle – Statistics are not Updated but are Created Once
- SQL SERVER – 2012 – Executing Stored Procedure with Result Sets – New
Code reuse is not the entire design decision, it is one consideration among the calling and execution requirements.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
36 Comments. Leave new
what kind of problem we are use fuction
WHAT KIND PROBLEM WE ARE CHANGE STORED PROCUDURE TO WRITE FUNTION USED
IN SQL SERVER? IF ANYONE KNOW PLS SHARE ME
It depends on the object, say a C# SQL statement call, consuming the results of the either the function (Table Valued Function) or the procedure (Returning a table AND execution success code). In some instances the function results cannot be consumed. And one may want to return multiple result sets (MARS) which is available only in the SP.
Thanks all
i got some idea how function and store procedure works
Great. Thanks Jeetesh!
Gatej Alexandru’s reply best describes the limitations of UDF’s.
I agree Roy.
Then why not use SP always
Usually I use stored procedures when doing complex logic that requires temp tables, exec statements etc. Stored procedures are sometimes more of a pain to use if I want to quickly and easily return a value and then use that value to join to other tables or data. Take for example two address fields from two tables both containing unclean data, and I want to join the two
Address Example (Not Cleaned) – “John Smith Address 1455 test avenue, beverly hills, ca, 90210 – Price $3,5000,000
Address Example 2 (Not Cleaned) – “1455 Test Ave, Beverly Hills, California, 90210”
Expected Clean Results from both fields – “1455 Test Ave, Beverly Hills, CA, 90210”
With a stored procedure, how would I go about doing this? I’m going to have to pass the fields to a stored procedure which can give me some output or will create a temporary table. Or I can clean up all of the data from a table and then use the cleaned data to do a join. This isn’t that different from a function but there are more steps in manipulating the output to do what I want than in a function. And since this is a simple cleansing task, I could create a function and join the results of the function directly, for example,
Select * from tblTestData td1 inner join tblTestData2 td2 on udf_CleanAddress(td1.address) = udf_CleanAddress(td2.address)
1. Select * means need all data to show and select count(*) means need to show all data count only