Top 10 Differences between Stored Procedure and Function in SQL Server

function-vs-stored-procedure-in-sql
Difference between Stored Procedure and Function in SQL Server

Stored procedures and functions are both database objects that contain a set of SQL statements that are used to execute a task. Both are different in many respects. In this post, we 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, and so on. So, let us start our discussion with an overview.

SQL Server – Stored Procedures

In SQL Server, a stored procedure is a precompiled set of T-SQL statements that are compiled and stored in the database. The stored procedure accepts input and output parameters, performs SQL statements, and returns a result set if necessary.

When you run a stored procedure for the first time, it compiles by default. It also generates an execution plan that is reused for subsequent runs, resulting in improved performance.

In layman’s terms, a stored procedure is a precompiled SQL statement that we save in the database so that we can reuse it multiple times by executing it.

To know more about a stored procedure, please refer to the article Different types of Stored Procedures in SQL Server.

SQL Server – Functions

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

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

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.whereas Stored Procedures cannot be called from a Function.
05.Stored Procedures cannot be used in a select statement.Whereas Function can be called 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.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, UDF can be used in the SELECT, WHERE, OR HAVING Statement.
function vs stored procedure

Example: Stored Procedure

The following is an example to create 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
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
as
Begin
    Return @num1 + @num2
End

-- Calling function in the SELECT statement
Select dbo.fn_AddNumbers(20,30) as result;
-- result: 50

FAQs

Q: What is the main difference between a stored procedure and a function?

The usage of the RETURNS and RETURN arguments is 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 it 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 support both SELECT and DML (INSERT/UPDATE/DELETE) statements, whereas a Function only supports SELECT statements. Procedures can`t be used in SELECT statements, but Functions can.

Q: When would a function be preferable to a stored procedure in SQL?

Stored procedures cannot be used anywhere in the SELECT/WHERE/HAVING/ section of a SQL statement, but functions can.

Summary

This blog provides detailed information on 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 have only input parameters, whereas Procedures can have both 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, Sqlshack, dotnettricks

Recommended Articles:

Leave a Comment