- 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
CATCHblock can not be used in a function.
- Stored procedure can not be used in
JOINclause. Function can be used in
- 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
DELETE. Function can not perform DML operations. It can use only be used in
EXECUTEcommand is used to executed the stored procedure. Stored procedures can not be used in
HAVINGetc. Function can be called from a
- Stored procedure can use explicit transaction
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.