Top 10 Differences between Stored Procedure and Function in SQL Server

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.

Difference between Stored Procedure and Function in SQL Server

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>

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>
 	<SQL statements>
    RETURN expression

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.
function vs stored procedure

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)
print 'The sum of '+cast(@num1 as varchar)+' + '+cast(@num2 as varchar)+' = '+ cast((@num1+ @num2) as varchar)
-- 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
SQL Server Stored procedure result

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
    Return @num1 + @num2

-- 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:

Let others know about this post by sharing it and leaving your thoughts in the comments section.

5 1 vote
Article Rating
Notify of

Inline Feedbacks
View all comments