Differences Between Stored Procedures and Functions in SQL Server

Differences between Stored Procedures and Functions

  1. Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters.
  3. Function allows only SELECT statement but Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it.
  4. We can't do Transaction Management in Function whereas in Procedure we can do Transactions Management.
  5. Functions can be called from Procedure whereas the vice versa is not possible.
  6. Exception cannot be handled by try-catch block in a Function whereas in a Procedure try-catch block can be used.
  7. While retrieving the values we can use SELECT statement in Function whereas in Procedures we cannot use SELECT statement to retrieve the data instead of that we have to use EXEC. 
Ebook Download
View all
Learn
View all