What is the difference between SQL Server stored procedures and User Defined functions (UDF)?
Following are the some major difference between Stored procedures and User Defined functions
Return a value
Stored procedure may or may not return a value while UDF function must return a value.
Support DML operations
Stored Procedure supports select statement as well as DML operations like we can use insert, update and delete statement while UDF function does not support DML, allow only select statement.
Parameters
Stored Procedure supports both type of parameters input and output while UDF function supports only input parameter, does not support output parameter.
Try.. catch block
Store Procedure supports TRY.. CATCH block to handle an exception while UDF function does not support TRY..CATCH block.
Use with Join clause
Stored procedure cannot be used in join clause as a result set while UDF function can be used in join clause.
Transactions
Stored procedure supports transactions while UDF function does not support transactions.
Execution
Stored procedure can be called through EXEC or EXECUTE statement, it cannot be called from select statement while function can be called from Select statement.
Use of Temporary tables
Stored procedure allows temporary tables while UDF Function does not allow temporary tables.
Calling UDF function/stored Procedure
Function can be called from stored procedure while stored procedure cannot be called from function.
Also Read..
