Stored Procedure vs UDF in sql

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 and use Dynamic SQL.
- Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
- Can use used with XML FOR clause.
- Can use a UDF inside a SP in SELECT statement.
- 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.
- Cannot execute an SP inside a UDF.
- Can be used to create Constraints while creating a table.
- Cannot execute some non-deterministic built-in functions, like GETDATE().

Comments