Monday, 24 September 2012

Stored Procedures vs Functions (Difference between SP & UDF) – Sql Server



Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.
On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.

Following are some common differences between an SP & a UDF:
Stored Procedures:
1.    Can be used to read and modify data.
2.    To run an SP Execute or Exec is used, cannot be used with SELECT statement.
3.    Cannot JOIN a SP in a SELECT statement.
4.    Can use Table Variables as well as Temporary Tables inside an SP.
5.    Can create and use Dynamic SQL.
6.    Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
7.    Can use used with XML FOR clause.
8.    Can use a UDF inside a SP in SELECT statement.
9.    Cannot be used to create constraints while creating a table.
10. Can execute all kinds of functions, be it deterministic or non-deterministic.
  
Functions:
1.    Can only read data, cannot modify the database.
2.    Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
3.    Can JOIN a UDF in a SELECT statement.
4.    Cannot use a Temporary Table, only Table Variables can be used.
5.    Cannot use a Dynamic SQL inside a UDF.
6.    Cannot use transactions inside a UDF.
7.    Cannot be used with XML FOR clause.
8.    Cannot execute an SP inside a UDF.
9.    Can be used to create Constraints while creating a table.
10. Cannot execute some non-deterministic built-in functions, like GETDATE().

No comments:

Post a Comment