Difference between Function and Stored Procedure

Q : What is the difference between a User Defined Function (UDF) and a Stored Procedure (SP) in SQL Server 2008 Ans:   1. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement. 2. Stored Procedure support differed name resolution where as functions do not support differed name resolution. 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. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables. 8. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL. 9. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.