Sql Server- Difference between Stored Procedure and Function

Imp SQL Difference Interview Questions SQL Functions SQL Server SQL Stored Procedure
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 thoughts on “Sql Server- Difference between Stored Procedure and Function

  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.

  2. 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

    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

Leave a Reply