Difference between Stored Procedures and Functions

Difference between Stored Procedures and Functions

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