Stored procedures and functions are database objects that contain a set of SQL statements used to execute a task. Both are different in many respects. This post will look at the differences between stored procedures and functions.
Before diving into the differences, it is important to understand the fundamental concepts such as stored procedures, functions, advantages, basic syntax, etc. Let’s start our discussion with an overview.
Table of Contents
- 1 What is stored procedure?
- 2 What is function in SQL?
- 3 Function vs Stored Procedure in SQL
- 4 FAQs
- 4.1 Q: What is the main difference between a stored procedure and a function?
- 4.2 Q: Which is faster, a stored procedure or a function?
- 4.3 Q: Can you call a function in a stored procedure?
- 4.4 Q: Is it possible for a procedure to return a value?
- 4.5 Q: Why would you create a SQL procedure rather than a function?
- 4.6 Q: When would a function be preferable to a stored procedure in SQL?
- 5 Summary
What is stored procedure?
In SQL Server, a Stored procedure is a precompiled set of T-SQL statements that are stored in the database. The stored procedure accepts input and output parameters, performs SQL statements, and returns a result set if necessary.
When you execute a stored procedure for the first time, it is compiled by default and creates an execution plan to reuse for subsequent runs, resulting in improved performance.
A stored procedure is a precompiled SQL statement that we save in the database to reuse multiple times by executing it.
To learn more about the stored procedure, please refer to the article Different types of Stored Procedures in SQL Server.
Syntax to create the stored procedure
CREATE PROCEDURE <procedure_name> AS BEGIN <sql_statements> END
Syntax to execute the stored procedure
EXECUTE <procedure_name> <parameters seprated by comma>
What is function in SQL?
Functions in SQL Server contain SQL statements that are compiled and executed every time they are called to perform specific tasks. A Stored procedure may or may not return a value, but a User defined function (UDF) must return a value and cannot modify the data received as input parameters.
A User Defined Function can return a single or scalar data value, such as an int, char, double, or decimal. It can also be table-valued, which takes parameters and returns a table as an output.
Syntax to create a user-defined function (UDF)
CREATE FUNCTION <function_name> (<@paramater_name> <datatype>) RETURNS <return_datatype> AS BEGIN <SQL statements> RETURN expression END
Syntax to execute a user-defined function (UDF)
SELECT * FROM <Function_name>(Parameter) -- OR SELECT Field1,Field2 <function_name(parameters)> FROM table_name
Function vs Stored Procedure in SQL
The following table describes the differences between functions and stored procedures in a SQL Server database.
|No.||Stored Procedure (SP)||User Defined Function (UDF)|
|01.||The Stored Procedure can return zero, single or multiple values.||The Function must return a single value which might be a scalar or a table.|
|02.||We can pass both input/output parameters in stored procedures.||A user-defined function in SQL Server can only accept input parameters.|
|03.||We can use the Try-Catch block to handle exceptions in the Stored Procedure.||We can’t use the Try-Catch block in Function.|
|04.||We can call functions from the Stored Procedure.||At the same time, We cannot call stored Procedures from a Function.|
|05.||We cannot use stored Procedures in a select statement.||At the same time, We can use a Function in a select statement.|
|06.||A stored procedure can perform any operation on database objects, including select and DML statements like insert, update, and delete.||Function in SQL only allowed Select statements.|
|07.||We can use the Transactions in Stored Procedure.||Transactions are not possible in function.|
|08.||A stored procedure can not be used in the Join clause.||A User defined function(UDF) in SQL Server can be used in a join clause as a result set.|
|09.||A stored procedure can have both table variables and temporary tables,||whereas a function in SQL Server can only have table variables because temporary tables are not allowed.|
|10.||We cannot use Stored Procedure (SP) anywhere in the WHERE, HAVING, OR SELECT statement of SQL queries.||However, we can use UDF in the SELECT, WHERE, OR HAVING Statement.|
Example: Stored Procedure
The following is an example of creating a Stored procedure in SQL Server to add two numbers.
-- Create a sample stored procedure Create procedure AddNumbers(@num1 float, @num2 float) as begin print 'The sum of '+cast(@num1 as varchar)+' + '+cast(@num2 as varchar)+' = '+ cast((@num1+ @num2) as varchar) end -- Execute stored procedure to print two numbers Execute AddNumbers 20, 30;
If you execute the above code, you will be able to see the following result:
The sum of 20 + 30 = 50
Example: Function (UDF) in SQL Server
Let’s create a sample example of a Scalar Function (UDF)
-- Create a sample user defined function in SQL Server Create function fn_AddNumbers(@num1 int,@num2 int) returns int as Begin Return @num1 + @num2 End -- Calling function in the SELECT statement Select dbo.fn_AddNumbers(20,30) as result; -- result: 50
Q: What is the main difference between a stored procedure and a function?
The RETURNS and RETURN arguments are required in functions (UDF ) but not in stored procedures.
Q: Which is faster, a stored procedure or a function?
The Stored procedures are faster than function as they are pre-compiled.
Q: Can you call a function in a stored procedure?
Yes, You can call a function from a stored procedure, but you cannot call a procedure from a function.
Q: Is it possible for a procedure to return a value?
A stored procedure cannot return value but may accept input/output parameters. A stored procedure can use an output parameter to return results.
Q: Why would you create a SQL procedure rather than a function?
A Stored Procedure supports SELECT and DML (INSERT/UPDATE/DELETE) statements, whereas a Function only supports SELECT statements. We can’t use procedures in SELECT statements, but Functions can.
Q: When would a function be preferable to a stored procedure in SQL?
We cannot use stored procedures anywhere in a SQL statement’s SELECT, WHERE, or HAVING section, but functions can.
This blog provides detailed information about SQL Server stored procedures and functions. Now you understand the difference between stored procedures and functions and how to use them for your basic requirement.
- In SQL Server, A function must return a value, which is optional in a Stored Procedure.
- A Function can only have input parameters, whereas Procedures can have input and output parameters.
- You can call a function from a stored procedure, but you cannot call a procedure from a function in SQL Server.
Reference: tutorials point: function vs stored procedure
You might want to read this too:
- Having VS Where Clause in SQL
- Function vs Stored procedure in SQL Server
- Stored Procedure in SQL Server – A Complete Guide [with Examples]
- COALESCE in SQL With Examples and Use Cases for Handling NULL Values
- SQL Server CONVERT Function: How to Convert Data Types in SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
- Primary Key Constraint In SQL Server
- View In SQL | Types Of Views In SQL Server
- SQL Server Trigger Update, Insert, Delete Examples
- SQL pivot tables
- Different ways to delete duplicate rows in SQL Server
- UNION vs UNION ALL in SQL SERVER
- SQL Server Try Catch: Error Handling in SQL Server With [Examples]
- Group by Month in SQL
Let others know about this post by sharing it and leaving your thoughts in the comments section.
- SQL Server Indexing: Clustered vs Non Clustered Index Explained - March 26, 2023
- Mastering Database Normalization: Best Practices and Techniques - March 25, 2023
- CRM Databases: The Key to Enhanced Customer Engagement and Sales - March 23, 2023