Frequently we have come to a point to decide which one to use for a scenario, Stored Procedure or User Defined Function (UDF). Before looking on the usage of the function and stored, we will see the comparison of function vs stored procedure in tabular form.
User Defined Function vs Stored Procedure
||User Defined Function (UDF)
|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.