Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
UDF’s cannot return Image, Text where as a StoredProcedure can return any datatype.
In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
UDF should return a value where as Stored Procedure need not.
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.
Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.
You can not write PRINT Statement in UDF
You can not write EXEC(‘any sql statement’) inside UDF
The core difference is that function has no side effect which means that it cannot change anything outside the function body.
Funcation can take only input parameter Where as Stored prcedure can take input and ouput parameters.
Stored procedures are compiled objects where as UDF are not
Stored procedures have capability to return multiple data sets where as function can return only one data set either scalar or table. May be this can be one of the reasons that the SP cannot be called inside UDF…Pl comment.
Functions can be called from procedure whereas procedures cannot be called from function.