Top 10 Differences between Stored Procedure and Function in SQL Server

Last updated on February 18th, 2023 at 02:27 pm

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.

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.

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;

Output:

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

FAQs

Summary

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.

• Author
• Recent Posts

Shekh Ali

Meet Shekh Ali, a skilled Software engineer and passionate blogger with an MCA degree and 8+ years of experience in the field. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Shekh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• 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