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