Tuesday, May 1, 2012

Difference between Function and Stored procedure

The following are the differences between a User Defined Function and a Stored Procedure in SQL Server

  1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
  2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
  3. UDF’s cannot return Image, Text where as a StoredProcedure can return any datatype.
  4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
  5. UDF should return a value where as Stored Procedure need not.
  6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.
  7. You cannot use non-deterministic built-in functions in UDF’s. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures.
  8. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
  9. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
  10. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

No comments:

Post a Comment