This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject.
Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)
Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once
Keeping the same spirit up, I am asking the third question over here.
Q3) When to use User Defined Function and when to use Stored Procedure in your development?
Personally, I believe that they are both different things ‑ they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.
When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other?
If you are going to answer that ‘To avoid repeating code, you use Function’ ‑ please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ ‑ again think harder! (link).
Now, what do you say? I will post the answers of all the three questions with due credit next week.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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