SQL Server - Differences between Stored Procedures and User Defined Functions

The below are the key differences between SP and UDF's .

S.No	Stored Procedures	User Defined Functions
1	Stored Procedure cannot be used in a Select statement	User Defined Function can be used in a Select statement
2	Stored procedure supports Deferred Name Resolution	User Defined Function does not support Deferred Name Resolution
3	Stored Procedures are generally used for performing Business Logic	User Defined Functions are generally used for Computations
4	Stored Procedure need not return a value	User Defined Functions should return a value
5	Stored Procedures can return any datatype	User Defined Functions cannot return Image
6	Stored Procedures can accept more number of input parameters than User Defined Functions. Stored Procedures can have upto 21000 input parameters	User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters
7	Stored Procedures can use Temporary Tables	Temporary Tables cannot be used in a User Defined Function
8	Stored Procedures can execute Dynamic SQL	User Defined Functions cannot execute Dynamic SQL
9	Stored Procedure supports error handling	User Defined Function does not support error handling.  RAISEERROR or @@ERROR are not allowed in UDFs
10	Non-deterministic functions can be used in Stored Procedures.	Non-deterministic functions cannot be used in User Defined Functions (UDFs).  For example, GETDATE() cannot be used in User Defined Functions(UDFs)

11      We can implement transactions in SP                    We cannot implement transactions in UDF's.

12     We can use DML,DDL and DQL commands          We can use only DQL (Select) in UDF's

         in SP

13.    To execute SP we have to use EXEC                       To execute function we have to use 'Select'

14.    Procedure will return a value                                     Function returns a value if return type is 

         with OUTparameters                                                 INT and returns a row if returns type is                                                                                                     table