SQL STORED PROCEDURE Vs USER DEFINED Function
The major difference between a User Defined Function and a Stored Procedure is that the former normally generates a return value and must be included in the SQL statement for it to be performed, whilst the latter does not always generate a return value, is optional, and can return zero in addition to other values.
SQL STORED PROCEDURE Statement
A stored procedure is a collection of SQL statements with a specific name that are physically saved in compiled form in a database.
Client applications can execute the stored procedure again and over again without having to send it to the database server or compile it again once it has been "stored".
By lowering network traffic and CPU load, stored procedures improve performance.
SQL USER DEFINED Function
A user defined function is a database object that encapsulates one or more SQL statements and returns either a value or a table.
It can receive zero or more parameters and return either a value or a table.
User-defined functions (UDFs) include helpful logic for leveraging other queries or database objects, such as stored procedures.
SQL Difference Between USER DEFINED FUNCTION And STORED PROCEDURE Statement
There are 14 main distinguish in user defined function & stored procedure :-
| USER DEFINED FUNCTION | STORED PROCEDURE |
|---|---|
| Returning a value is required for a User defined function. | The value of the return parameter is optional. Can return a single value or several values. |
| To be performed, it must be part of a SQL statement. | EXECUTE or EXEC command can be used to run it. |
| Functions can be accessed using Procedure. | Procedures cannot be invoked from a UDF. |
| Input parameters are the only parameters that a user written function can have. | Input and output parameters can be used in a procedure. |
| They are unable to change the data they receive as arguments. Functions are not allowed to change anything, to put it another way. | Stored procedures have the ability to alter database objects. |
| Inline User-Defined Functions can be used in row set operations and JOINS, just as views with arguments. | Table variable as a return type is not supported. |
In SQL statements, user written functions can be used anywhere in the WHERE / HAVING / SELECT sections. |
Cannot be used anywhere in the WHERE / HAVING / SELECT parts of SQL queries. |
We can only utilise the SELECT statement in a function. |
We can utilise SELECT, INSERT, UPDATE, and |
Only SELECT statements are allowed in it. |
Supports both SELECT and DML commands. |
| It is not supported. Catch blocks is a good option. | We may utilise the TRY-CATCH block in a Procedure to handle the exception. |
| Supports all data types supported by SQL Server. | Some data types, such as ntext, picture, and timestamp, cannot be used as return types. |
| Transaction management is not supported. | Transaction Management should be supported. |
| Temporary tables are not allowed in a user defined function. | We can use temporary variables, tables, and CTE while writing a Procedure. |
| Every time a user-defined function is called, it is compiled. | When a Procedure is invoked, it is compiled once and then run. |