SQL SERVER – Question to You – When to use Function and When to use Stored Procedure

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?

SQL SERVER - Question to You - When to use Function and When to use Stored Procedure fvssp-800x387

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)

, ,
Previous Post
SQL SERVER – Recycle Error Log – Create New Log file without Server Restart
Next Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31

Related Posts

36 Comments. Leave new

  • what kind of problem we are use fuction

    Reply
  • WHAT KIND PROBLEM WE ARE CHANGE STORED PROCUDURE TO WRITE FUNTION USED
    IN SQL SERVER? IF ANYONE KNOW PLS SHARE ME

    Reply
  • 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.

    Reply
  • Thanks all
    i got some idea how function and store procedure works

    Reply
  • Roy Fulbright
    July 20, 2015 10:30 pm

    Gatej Alexandru’s reply best describes the limitations of UDF’s.

    Reply
  • Anurag Nayak
    June 27, 2016 2:01 pm

    Then why not use SP always

    Reply
  • 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)

    Reply
  • 1. Select * means need all data to show and select count(*) means need to show all data count only

    Reply

Leave a Reply

Menu