Differences between functions and stored procedures in SQL Server

Overview

Some of the differences between functions and stored procedures are listed below:

Functions

• We can embed functions in SELECT statements.
• Functions only allow SELECT statements.
• In functions, we cannot use a try-catch block to handle exceptions.
• In functions, we cannot use transactions.
• We cannot call a function from the stored procedures.
• Functions do not support output parameters.
• Function must have a return value.

Procedures

• We cannot use the procedures in SELECT statements.
• Procedures allow SELECT statements and DML(update/delete/insert) statements.
• In procedures, we can handle the exceptions using the try-catch block.
• Procedures allow the use of transactions.
• We can call a function from a stored procedure.
• Procedures support the input and output parameters.
• A return value is optional for procedures.