Let us first understand these two terms by their definition. Stored Procedures are pre-compiled objects which are compiled for the first time and the compile format is saved, which gets executed whenever it is called, whereas SQL Server Function is compiled and executed every time it is called.
Being a developer we have to deal with creation as well as modification of existing stored procedures in the database. Apart from procedures a developer also gets to create functions in SQL Server.
There are a few basic differences between the two. Let us list down the basic differences between them.
- Stored procedure returns output parameter whereas function does not return output parameter.
- We can use transactions in procedure whereas functions don't support transactions.
- UDF can be called from stored procedure whereas stored procedure can’t be called from function.
- We can use Insert, Update, and 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 re-used whereas in functions it is compiled every time.
I have to tried to list down the basic difference between stored procedure and function in SQL Server.