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 (http://blog.SQLAuthority.com)