Home » Differences between User Defined Functions and Stored Procedures

Differences between User Defined Functions and Stored Procedures

First lets look at the two briefly and why they are useful.
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.

User Defined Functions or UDFs:can contain single or multiple SQL statements depending on its type. A Scalar UDF and 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 Stored Procedure and User Defined Functions:

Stored Procedures:
- Can be used to read and modify data.
- To run an SP Execute or Exec is used, cannot be used with SELECT statement.
- Cannot JOIN a SP in a SELECT statement.
- Can use Table Variables as well as Temporary Tables inside an SP.
- Can create table but won’t return Table Variables
- Can create and use Dynamic SQL.
- Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
- Can use used with XML FOR clause.
- Stored procedures can call a funtion or another stored procedure.
- Cannot be used to create constraints while creating a table.
- Can execute all kinds of functions, be it deterministic or non-deterministic.

Functions:
- Can only read data, cannot modify the database.
- Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
- Can JOIN a UDF in a SELECT statement.
- Cannot use a Temporary Table, only Table Variables can be used.
- Cannot use a Dynamic SQL inside a UDF.
- Cannot use transactions inside a UDF.
- Cannot be used with XML FOR clause.
- Functions does not have pre compiled exuction plan
- A function can call another function and a stored proc excluding user defined stored procedures which means can call only extended/system defined stored procedures.
- Can be used to create Constraints while creating a table.
- Cannot execute some non-deterministic built-in functions, like GETDATE().

Leave a Reply

Your email address will not be published. Required fields are marked *