Description
In this article, I am going to explain about the difference between Stored Procedure and Function in SQL Server and summarize the key points.
Differences: Stored Procedure vs Function
The first difference that is seen with the user defined function is that it’s programmed in a way that it must return a value. The stored procedure does have some allowance on whether or not to return a value. This depends on whether or not the stored procedure has a value to return.
Another difference seen between the user defined function and the stored procedure regards statements. The user defined function only allows Select statements to be read while DML statements are not allowed. On the other hand, stored procedure allows use of both Select statements, as well as the DML statements, which can also be updated and manipulated.
The user defined function will only allow input of parameters but does not support output of the same parameters. stored procedure, on the contrary, supports both input and output parameters. The UDF also does not allow for the use of try-catch blocks. stored procedure allows for the use of the try catch blocks for exception handling.
The function also does not allow for transactions to occur within functions. This functionality is available in the stored procedure which allows for transaction handling. function also does not allow for the use of table variables and it also does not allow for temporary tables. stored procedure, however, allows for the use of table variables as well as a temporary table in it.
Summary: Stored Procedure vs Function
– It is mandatory for Function to return a value while it is not for Stored Procedure.
– Function accepts only Select statements.
– Stored procedure accepts Select statements as well as DML statements.
– Function only allows inputs and not outputs.
– Stored procedure allows for both inputs and outputs.
– Catch blocks cannot be used in Function but it can be used in Stored procedure.
– No transactions allowed in Function but in Stored procedure transactions are allowed.
– Only table variables can be used in Function and not temporary tables.
– Stored procedure allows for both table variables and temporary tables.
– Function does not allow stored procedures to be called from functions while Stored procedure allow calling of functions.
– Function is used in join clause while Stored procedure cannot be used in join clause.
– Stored procedure will always allow for return to zero. Function, on the contrary, has values that must come – back to a predetermined point.
Thanks,
Morgan
Software Developer