Tuesday, December 22, 2009

Sql Server- Difference between Stored Procedure and Function


In Sql Server, both functions and stored procedures can be defined as the logically grouping of Sql statements formed to complete a task, but still there are many differences between Stored procedure and Functions.

These differences can be summarized as follows:-

1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement. 

3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

6 comments:

  1. one more main diffrence between UDF and SPROC that UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can.

    ReplyDelete
  2. Thanks Amit for your valuable comments and sharing your invaluable knowledge.

    Regards
    Vivek

    ReplyDelete
  3. thanks vivek for sharing the articles.........

    ReplyDelete
  4. all of above 6 differences are very minute and not Very Useful for Your Concept.all of above example comes from experience not for excellence.
    I am share our knowledge with u--in a single line of statement

    Stored procedure is Business block in Database Domain just like Method In C#,Java,C,C++ where we have put all SQL statement. on the hand in Function we have only put that statement which can't change the database structure

    ReplyDelete
    Replies
    1. means We can't use any statement that modified database structure .some are statement are as like this which cant't use in Function but u can use these statement in SP(Stored Procedure)-
      Insert,update,Delete,Create,Alter,Drop,Truncate etc.

      if i am Wrong,Let Me Know

      Delete