Stored Procedure or User Defined Function (UDF)? In SQL Server database development, often we will be in a situation to decide which one to use for a given scenario. Before looking on the usage of the function and stored procedure, we will see a comparison between them. This will help you to decide on when to use a UDF and when to use a stored procedure.
User Defined Function vs Stored Procedure
| Sl.# | User Defined Function (UDF) | Stored Procedure |
| 1 | Function should return a value, either a scalar value or a table. | Stored procedure may or may not return value. It can even return multiple scalar values or tables |
| 2 | Function should have only input parameters. | Stored procedure can have both input and out parameters. |
| 3 | Functions should have at least one input parameter. | In stored procedures input parameters are optional. |
| 4 | A maximum of 1024 input parameters can be used in a function. | A maximum of 2100 parameters can be used in a stored procedure. |
| 5 | A function can be called from inside a stored procedure. | A stored procedure cannot be called from inside a function. |
| 6 | Functions cannot perform any permanent environmental change to the database. | Stored procedures can perform any permanent environmental change to the database. |
| 7 | Functions cannot use DML statements like INSERT, UPDATE or DELETE against any tables, temp tables or views. These statements can be used only against local table variables. | Stored procedures and use DML statements against permanent tables, temp tables or views. |
| 8 | Transaction management: Begin / Commit / Rollback Transactions cannot be used in functions. | Transactions can be used in stored procedure. |
| 9 | Exception or error handling using try/catch cannot be used in a function. | Exception or error handling is allowed to be used in stored procedure. |
| 10 | Function can be called from inside SELECT statement and WHERE or HAVING classes. | Stored procedure cannot be called from inside SELECT statement and WHERE or HAVING classes. |
| 11 | Table-Valued Function can be used in JOIN clause just like a table. | Stored procedures cannot be used in JOINs. |
| 12 | On using as function in SELECT, WHERE or HAVING clause, you can pass the column as a parameter. | Column cannot be passed as parameter in stored procedure. |
| 13 | Functions are normally used for computing small logic and return the result. | Stored procedures are normally used for highly complex business logic and either return the result or update the values in tables. |
| 14 | You cannot use temporary tables in functions. Instead they can use table variables. | In stored procedures you can use temp tables as well as table variables. |
| 15 | Non-deterministic built-in functions like NEWID,NEWSEQUENTIALID,RAND and TEXTPTR cannot be used in user defined function. | No such condition. |
| 16 | Function cannot be executed using EXECUTE or EXEC command. | Stored procedures can be executed using EXECUTE or EXEC command. |
Usage Of Function (UDF)
The major usage of User Defined Function is its ability to compute small business logic and return a value in the form of a single scalar or a table result set which can be used inline in a SELECT query or WHERE or HAVING clause.
Usage Of Stored Procedure
- Stored procedures are normally used for computing highly complex business logic and either return the result or update the values in tables.
- Stored procedures can have complex SQL statements which may perform permanent environmental changes in the database.
Related Article
Reference:
- See more about user defined function at msdn.