What is the difference between stored procedure and function in SQL?
Play this article
- Stored procedure can call a function. But function can not call a stored procedure.
- Stored procedure can use temporary tables and table variables. Function can use table variable but not temporary tables.
- Exceptions can be handled using
TRY
...CATCH
block.TRY
...CATCH
block can not be used in a function. - Stored procedure can not be used in
JOIN
clause. Function can be used inJOIN
clause. - Stored procedure can return zero, single or multiple values (i.e result set) (max 1024). Function must return a single result set. It could be either scalar or a table.
- Stored procedure can perform DML operations like
INSERT
,UPDATE
,DELETE
. Function can not perform DML operations. It can use only be used inSELECT
statements. EXEC
orEXECUTE
command is used to executed the stored procedure. Stored procedures can not be used inSELECT
,FROM
,WHERE
,HAVING
etc. Function can be called from aSELECT
,FROM
,WHERE
,HAVING
etc- Stored procedure can use explicit transaction
BEGIN TRAN
,COMMIT
,ROLLBACK
. Function can not use transaction. - Stored procedure is parsed, compiled and stored in database. Function is compiled at run time.
- Stored procedure can have both input and output parameter. Function can have only input parameter.