Home » SQL Server Stored Procedures Vs User Defined Functions

SQL Server Stored Procedures Vs User Defined Functions

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.

Recommended for you

Stored procedures

User Defined functions



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.