Difference between Function and Stored procedure

The following are the differences between a User Defined Function and a Stored Procedure in SQL Server

1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
3. UDF’s cannot return Image, Text where as a StoredProcedure can return any datatype.
4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
5. UDF should return a value where as Stored Procedure need not.
6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.
7. You cannot use non-deterministic built-in functions in UDF’s. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures.
8. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
9. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
10. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.