Stored Procedures are pre-compiled objects which are compiled for first time and its compile format is saved which gets executed whenever it is called.
Whereas SQL Server Function is compiled and executed every time when it is called.
Stored Procedure Vs. Function in SQL Server
- Stored procedure returns output parameter whereas function does not return output parameter.
- We can use transactions in procedure whereas functions doesn’t support transactions.
- UDF can be called from stored procedure whereas stored procedure can’t be called from function.
- We can use Insert,Update,Delete commands in procedure whereas in functions we can only use Select.
- Procedure supports exception handling whereas UDF doesn’t.
- Stored procedure can have input as well as output parameter whereas function can only have input parameter.
- Procedure can return zero or n values whereas function can return one value which is mandatory.
- Stored procedure uses the execution plan that can be reused whereas in functions it is compiled every time.
0 comments:
Post a Comment