In SQL Server, we usually come across queries, tables, views, stored procedures and functions. People often wonder what are the real differences between User Defined Functions (UDF) or simply functions and stored procedures or just procedures. There are two types of functions: built-in and user-defined. Built-in functions cannot be modified whereas you can create and modify your own UDF. To utilize the full advantage of these features, we should understand their capabilities and what we can do with them and what we cannot. I am going to explain this in terms of SQL Server.
They have some features in common between them,
- Both are stored in a database. This means the SQL statements inside them need not be sent across the network.
- Reduce network traffic. This is because both can replace very long and complex SQL queries, transmitted over the wire, to a single line.
- Both are compiled only once - when executed for the first time - and reduce the compilation cost by caching the execution plans and reusing them for repeated executions. This improves the performance tremendously when called repeatedly.
- Both allow automatic re-compilation if there is any change applied to them.
Below are the differences between User Defined Functions and Stored Procedures. So, you can use this table to check the functionalities you need. Then, you will be able to decide whether you should go for a UDF or a procedure.
Function (UDF) | Stored Procedure |
| Must return a value. | Return value is optional. Can return zero or more values. |
| Must be a part of an SQL statement to get executed. | Can be executed using EXECUTE or EXEC command. |
| Functions can be called from Procedure. | Procedures cannot be called from UDF. |
| Can have only input parameters. | Can have input/output parameters. |
| Cannot alter the data they receive as parameters. More accurately saying functions are not allowed to change anything. | SPs can change database objects. |
| Inline User-Defined Functions can be treated like views with parameters and can be used in row set operations and JOINs. | Cannot JOIN the output of a Stored procedure. |
| UDF can be used in the SQL statements anywhere in the WHERE / HAVING / SELECT sections. | Cannot be used in the SQL statements anywhere in the WHERE / HAVING / SELECT sections. |
| But inline functions support only one SELECT statement. | Supports any number of select statements. |
| Allows only SELECT statement in it. | Allows SELECT as well as DML statement |
| Does not support Try Catch blocks. | Try Catch blocks can be used. |
| Functions that return table variable can be treated as another row set and can be used in JOINs with other tables. | Does not support table variable as return type. |
| Can use all the data types that SQL server supports. | Cannot use some data types such as ntext, image, and timestamp as return type. |
| Does not support transaction management. | Support Transaction Management. |
This is how you create a function,
- CREATE FUNCTION[schema_name.]
- MyFunction(
- [
- @parameter1 data_type[ =
- default value]
- [, @parameter2 data_type, …n]
- ])
- RETURNS datatype
- AS
- BEGIN
- SQL STATEMENT
- RETURN value
- END
| This is how you create an SP,
- CREATE PROC | PROCEDURE[schema_name.] MyStoredProcedure
- ([
- @parameter1 data_type[ =
- default value]
- [, @parameter2 data_type[OUT | OUTPUT], …n]
- ])
- AS {
- [BEGIN] sql_statement[END]
- RETURN
- }
|
Conclusion
The above table will help you decide whether you need to go for a Stored Procedure or a Function but only if you know what is your actual requirement and how it may change in the future.
Thanks to references
- https://www.sqlservercentral.com/Forums/Topic416974-8-1.aspx
- http://www.dotnettricks.com/learn/sqlserver/difference-between-stored-procedure-and-function-in-sql-server
- http://www.c-sharpcorner.com/forums/what-is-difference-between-stored-procedure-and-functions
- https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/execute-user-defined-functions
- http://www.c-sharpcorner.com/UploadFile/skumaar_mca/differences-between-procedures-and-functions/
- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3c715b0e-8c30-4ee1-90b9-85216440625c/stored-procedure-vs-user-defined-function?forum=transactsql
- https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql