Differences between function and stored procedure in SQL

Before you decide in using function or stored procedure, you must know that a function cannot used SQL statement like INSERT, UPDATE and DELETE while a stored procedure can. A function also does not support error handling but a stored procedure support error handling (you must include the error handling). Below are more on differences between both:
  1. Function can be used in a select statement but a stored procedure can't.
  2. Functions are mostly used for computations while Stored Procedures are used for business logic.
  3. Function must return a value while Stored Procedure are optional.
  4. Function accept only input parameters while Stored Procedures can accept input and output parameters.
  5. GETDATE() or other non-deterministic functions is not allow in Function but are allow in Stored Procedures.
  6. StoredProcedure can use Temporary Tables while Function can't.
  7. Function can not Execute Dynamic SQL while a Stored Procedure can.


Post a Comment