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.
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.
Below are some common differences between an Function & Stored Procedure:
Functions:
* Cannot be used to change server configuration
* Cannot have transaction within function
* Not returning output parameter but returns Table variables
* Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
* Cannot execute an SP inside a UDF.
* Can only read data, cannot modify the database.
* Cannot use a Temporary Table, only Table Variables can be used.
* Cannot use a Dynamic SQL inside a UDF.
* Can JOIN a UDF in a SELECT statement.
* Cannot be used with XML FOR clause.
* Can be used to create Constraints while creating a table.
* Cannot execute some non-deterministic built-in functions, like GETDATE().
Stored Procedures:
* Have to use EXEC or EXECUTE, cannot be used with SELECT statement.
* Can create table but won’t return Table Variables
* Cannot JOIN a SP in a SELECT statement.
* Can be used to read and modify data.
* Can use Table Variables as well as Temporary Tables inside an SP.
* Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
* Can use a UDF inside a SP in SELECT statement.
* Can create and use Dynamic SQL.
* Can use used with XML FOR clause.
* Cannot be used to create constraints while creating a table.
* Can execute all kinds of functions, be it deterministic or non-deterministic.
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.
Below are some common differences between an Function & Stored Procedure:
Functions:
* Cannot be used to change server configuration
* Cannot have transaction within function
* Not returning output parameter but returns Table variables
* Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
* Cannot execute an SP inside a UDF.
* Can only read data, cannot modify the database.
* Cannot use a Temporary Table, only Table Variables can be used.
* Cannot use a Dynamic SQL inside a UDF.
* Can JOIN a UDF in a SELECT statement.
* Cannot be used with XML FOR clause.
* Can be used to create Constraints while creating a table.
* Cannot execute some non-deterministic built-in functions, like GETDATE().
Stored Procedures:
* Have to use EXEC or EXECUTE, cannot be used with SELECT statement.
* Can create table but won’t return Table Variables
* Cannot JOIN a SP in a SELECT statement.
* Can be used to read and modify data.
* Can use Table Variables as well as Temporary Tables inside an SP.
* Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
* Can use a UDF inside a SP in SELECT statement.
* Can create and use Dynamic SQL.
* Can use used with XML FOR clause.
* Cannot be used to create constraints while creating a table.
* Can execute all kinds of functions, be it deterministic or non-deterministic.
