Differences Between Stored Procedures and Functions in SQL Server

We have seen most of the freshers and also experienced are confused when to use Functions and when to use Stored Procedures. So I have covered some differences between Functions and Stored Procedures, which will help to decide you.

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.