Difference between Stored Procedure and Function in Sql Server
Sql Server’s two Objects Procedure and Function seems almost same as both we used to write the query but there is huge difference between procedure and Function as per concept and logically use of both it . We will see Stored procedure (SP) VS User Defined Functions.
Key Difference between Stored Procedure and Function in Sql Server
- Procedure can have input/output parameters while User Define Function have only input parameters.
- Procedure can not be called from Function while Function can be called from Procedure.
- Procedure return zero or n Values which is optional while Function must have to return scalar or table values.
Advance Difference between Stored Procedure and Function in Sql Server
- Procedure can perform one or more task while Function can not perform specific task.
- For Exception handling we can use Try – Catch block in Procedure while can not use Try – Catch block in Function.
- Procedure can be used to change Server configuration while Function Can not be used to change Server Configuration.
- Procedure can be used with XML for Clause while Function can not be used with XML for clause.
- With Procedure we can use Transaction Management while we can not use Transaction Management in Functions.
- Procedure is precomplied plan while Function is Complied and execute every time.
- With Procedure we can use Select as well as all DML(Insert/Update/Delete) statements while in Function We can use only Select Statement .
- Procedure can not be utilized in select statement and have to use with EXEC and EXECUTIVE while Function can be embedded in Select Statements.
- Procedure can not be used in the SQL statements any where like where,having ans select sections while Function can be used.
- Procedure are normally used for executing Business Logic while Function is normally used for computation.
- Function returns table which is another Row Set so can be used to join with other Table and we can also join Function with each other while We can not Join Procedure.
- Procedure support differed name solutions while Function does not support.
What is your opinion ?
Here I have tried my best to justify Stored procedure(SP) vs User Define Function(UFD) . I hope this will be helpful to you understand the difference between stored procedure and Function . If you have any query you can comment or you can mail me.