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.
- See more about user defined function at msdn.