Differences between Stored Procedures and
Functions
- Function must return a value but in
Stored Procedure it is optional (Procedure can return zero or n values).
- Functions can have only input parameters
for it whereas Procedures can have input/output parameters.
- Function allows only SELECT statement but
Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in
it.
- We can't do Transaction Management in
Function whereas in Procedure we can do Transactions Management.
- Functions can be called from Procedure
whereas the vice versa is not possible.
- Exception cannot be handled by try-catch
block in a Function whereas in a Procedure try-catch block can be used.
- 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.