Rajanand Ilangovan
Rajanand Ilangovan

Follow

Rajanand Ilangovan

Follow

What is the difference between stored procedure and function in SQL?

Rajanand Ilangovan's photo
Rajanand Ilangovan
·May 22, 2022·

1 min read

  1. Stored procedure can call a function. But function can not call a stored procedure.
  2. Stored procedure can use temporary tables and table variables. Function can use table variable but not temporary tables.
  3. Exceptions can be handled using TRY...CATCH block. TRY...CATCH block can not be used in a function.
  4. Stored procedure can not be used in JOIN clause. Function can be used in JOIN clause.
  5. 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.
  6. Stored procedure can perform DML operations like INSERT, UPDATE, DELETE. Function can not perform DML operations. It can use only be used in SELECT statements.
  7. EXEC or EXECUTE command is used to executed the stored procedure. Stored procedures can not be used in SELECT, FROM, WHERE,HAVING etc. Function can be called from a SELECT, FROM, WHERE, HAVING etc
  8. Stored procedure can use explicit transaction BEGIN TRAN, COMMIT, ROLLBACK. Function can not use transaction.
  9. Stored procedure is parsed, compiled and stored in database. Function is compiled at run time.
  10. Stored procedure can have both input and output parameter. Function can have only input parameter.