Difference between Functions and Stored Procedures in SQL Server
The following table lists the difference between Functions and Stored Procedures in SQL Server database.
| Function | Stored Procedure |
|---|---|
| Always returns a single value; either scalar or a table. | Can return zero, single or multiple values. |
| Functions are compiled and executed at run time. | Stored procedures are stored in parsed and compiled state in the database. |
| Only Select statements. DML statements like update & insert are not allowed. | Can perform any operation on database objects including select and DML statements. |
| Allows only input parameters. Does not allow output parameters. | Allows both input and output parameters |
| Does not allow the use of Try…Catch blocks for exception handling. | Allows use of Try…Catch blocks for exception handling. |
| Cannot have transactions within a function. | Can have transactions within a stored procedure. |
| Cannot call a stored procedure from a function. | Can call a function from a stored procedure. |
| Temporary tables cannot be used within a function. Only table variables can be used. | Both table variables and temporary tables can be used. |
| Functions can be called from a Select statement. | Stored procedures cannot be called from a Select/Where or Having statements. Execute statement has to be used to execute a stored procedure. |
| Functions can be used in JOIN clauses. | Stored procedures cannot be used in JOIN clauses |