Stored procedures(SP) Vs Functions(UDF)

	Stored Procedures	Functions
Use	Used to read and modify data.	Used only to read data
Execution	Execute or Exec is used to execute SP	Can used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
Joins	Cannot JOIN a SP in a SELECT statement.	Can JOIN a UDF in a SELECT statement.
Temp objects	Can use Table Variables as well as Temporary Tables inside an SP.	Cannot use a Temporary Table, only Table Variables can be used.
Dyanamic SQL	Can create and use Dynamic SQL.	Cannot use a Dynamic SQL inside a UDF.
Transaction	Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.	Cannot use transactions inside a UDF.
XML	Can use used with XML FOR clause.	Cannot be used with XML FOR clause.
UDF	Can use a UDF inside a SP in SELECT statement.	Cannot execute an SP inside a UDF.
Constraint	Cannot be used to create constraints while creating a table.	Can be used to create Constraints while creating a table.
Execute	Can execute all kinds of functions, be it deterministic or non deterministic.	Cannot execute some non-deterministic built-in functions, like GETDATE().

Published by Manish Kaushik