Difference between Stored Procedure and User Defined Function in Sql Server
Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.
To know more on the User-Defined functions with examples please visit the article: User-Defined function.To know more on the Stored Procedure with examples please visit the article: Stored Procedure.
| Sl. No. | User Defined function | Stored Procedure |
| 1 | Function must return a value. | Stored procedure may or not return values. |
| 2 | Will allow only Select statement, it will not allow us to use DML statements. | Can have select statements as well as DML statements such as insert, update, delete etc |
| 3 | It will allow only input parameters, doesn’t support output parameters. | It can have both input and output parameters. |
| 4 | It will not allow us to use try-catch blocks. | For exception handling we can use try catch blocks. |
| 5 | Transactions are not allowed within functions. | Can use transactions within Stored procefures. |
| 6 | We can use only table variables, it will not allow using temporary tables. | Can use both table variables aswell as temporary table in it. |
| 7 | Stored procedures can’t be called from function. | Stored Procedures can call functions. |
| 8 | Functions can be called from select statement. | Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure. |
| 9 | UDF can be used in join clause as a result set. | Procedures can’t be used in Join clause |
Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.
SPs are used to return one or many result-sets to its calling application.
On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.
Following are some common differences between an SP & a UDF:
Stored Procedures:
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.
Functions:
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().
No comments:
Post a Comment