SQL Server stored procedure vs function

by

Both stored procedures and functions are database objects in SQL Server, and both can store multiple SQL statements used for a certain operation. Still, both of these objects are different from one another in numerous ways.

So, in this article, we will discuss some of the main differences between SQL server stored procedure and function, and we will cover the following topics.

  • SQL Server stored procedure vs function
  • SQL Server stored procedure vs function performance
  • SQL Server stored procedure vs table-valued function
Function vs stored procedure in SQL Server
SQL Server stored procedure vs function

SQL Server stored procedure vs function

In this section, we will try to explain some key differences between function and a stored procedure in SQL server.

Stored Procedure in SQL Server

  • A stored procedure is a database object in SQL Server. It compiles and stores a series of SQL statements that we need to execute frequently.
  • When we create a stored procedure in SQL Server, the SQL Server compiles the procedure and creates a execute plan for it. And SQL Server will use the execution plan to execute the procedure.
  • A stored procedure is a pre-compiled object and will be valid till we make some changes.
  • Stored Procedures have the advantage of being executed on the server and performing a set of tasks before returning the results to the client.
  • A stored procedure can accepts inputs using input parameters and return the output using output parameters.

Here is the general syntax of creating a stored procedure in SQL Server.

CREATE PROCEDURE procedure_name
AS

BEGIN
  sql_statements
END

For example, let’s create a simple stored procedure to print a message.

CREATE PROCEDURE usp_simpleprocedure
AS
BEGIN
  PRINT 'Welcome to sqlserverguides'
END

Next, to execute a procedure, we have to use the EXEC statement. The query to execute the procedure is given below.

USE sqlserverguides  --sqlserverguides is the database name
GO

EXEC usp_simpleprocedure
GO

After executing the above example, we will get the following result.

SQL Server stored procedure vs function
Output

Function in SQL Server

  • A function is also a database object in SQL Server. It’s essentially a sequence of SQL statements that take only input parameters, execute tasks, and return the output.
  • A function in SQL Server is compiled and executed whenever it is called. Additionally, a function can only return a single value or table.

Here is the general syntax of creating a function in SQL Server. 

CREATE FUNCTION [schema_name.] function_name()
RETURNS return_data_type
AS 
BEGIN
  sql_statement
END

For example, let’s create a simple function to print a message. 

CREATE FUNCTION dbo.simplefunction()
RETURNS varchar(30)
AS 
BEGIN
  RETURN 'Welcome to sqlserverguides'
END

Next, to call a function, we have to use the SELECT statement. The query to call the function is given below.

USE sqlserverguides
GO 

SELECT dbo.simplefunction() AS 'Message'
GO

After executing the above example, we will get the following output.

SQL Server function vs stored procedure
Output

Key Differences

Let’s discuss some of the major differences between SQL server store procedure and function.

  • A stored procedure in SQL Server can have input as well as output parameters. A function, on the other hand, can only have input parameters.
  • A function can only return one value, whereas a stored procedure can return numerous parameters.
  • A function in SQL Server must return a value. However, it is optional in a Stored Procedure.
  • We can use a function within a stored procedure but, we cannot use a procedure in a function.
  • We can handle errors in a stored procedure by using the TRY-CATCH block. But, we cannot use TRY-CATCH in functions.
  • We can use SELECT as well as DML(INSERT/UPDATE/DELETE) statements in a stored procedure. But, we can only use SELECT statements within a function, as DML statements are not supported in functions.
  • We can easily use functions in a SELECT statement. But, we cannot use a stored procedure in a SELECT statement.
  • Similarly, we can use the functions in WHERE/HAVING/SELECT section. But, a procedure is not supported to be used in these statements.
  • We can use the transactions in the stored procedure. But, we cannot use it in functions.
  • In SQL Server, a function that returns a table can be considered a separate rowset.

SQL Server stored procedure vs function performance

Both stored procedure and function are database objects in SQL Server. And, many developers often get confused about when to create which object. So, in this section, we will discuss the performance difference between a procedure and function.

A query executed within a function and one executed within a procedure have almost the same speed. So, query execution speed is not a major factor in terms of performance.

The main factor that affects the performance of a procedure or function is the situation in which they are used. So, here are some of the points that we can consider while using function or procedure.

  • A function can only use the SELECT statement in it. We cannot use other DML, DDL statements within a function. For this purpose, we can use a stored procedure. Stored procedures support the use of all types of DML, DDL, SELECT statements in it.
  • On the other hand, we cannot use a stored procedure in SELECT or DML statements. For this objective, we can use a SQL Server function. A function can be easily used in SELECT or DML statements.
  • A function in SQL server can only return single value or table but what if we can multiple values. In such case, we can use stored procedure to return multiple values as output.

The selection of these objects should be based on requirements rather than performance. Anything that outputs a dataset should be a view or a table-valued function. Any data manipulation must be done through a procedure.

SQL Server stored procedure vs table-valued function

In this section, we will discuss the detailed comparison between a table-valued function and a stored procedure.

Table-valued function in SQL Server

A user-defined function that returns data of the table type is known as a table-valued function. And we can utilize the returned data in the same way as we use a table.

To create a table-valued function in SQL Server, we can use the following syntax given below.

CREATE FUNCTION function_name (
    @input_parameter datatype,    --optional to use
    ...
)
RETURNS TABLE
AS

  sql_statement                   --our sql queries

Let’s understand this implementation with the help of an example.

USE sqlserverguides
GO

CREATE FUNCTION udf_GetDeptRecords (
    @dept_name VARCHAR(50)
)
RETURNS TABLE
AS
RETURN SELECT [name], [Department] FROM [DeptTable]
       WHERE [Department] = @dept_name

In the above example, we have created a user-defined function that accepts an input and returns table-valued data. This function will accept a department name and return all the records of that department from a table.

Next, we can easily use a SELECT statement to use this function and get the result. Here is the query that we will execute to get the output.

USE sqlserverguides
GO

SELECT * FROM udf_GetDeptRecords('Legal')
GO

We are executing the above script to get the records of the legal department from the table. And we will get the following output.

SQL Server stored procedure vs table-valued function
Output

Read: How to check if SQL Server is running

Stored procedure to get a table data

For a table-valued function, we can directly use a SELECT statement to fetch the table value from the function. But in the case of a stored procedure, we cannot implement this directly. For the stored procedure, first, we need to create a table that can store the result. After this, we have to insert the values from the procedure into the table.

For this demonstration, let’s create a stored procedure.

USE [sqlserverguides]
GO

CREATE PROCEDURE [dbo].[GetDeptRecords]
( @dept_name VARCHAR(50) )
AS
BEGIN
SET NOCOUNT ON
 
SELECT [Name], [Department] FROM DeptTable
WHERE [Department] = @dept_name
 
END
GO

In the above example, we have created a stored procedure that will also accept a department name as an input. And it will return all the records of that department from the department table.

As in the example, we are selecting two columns from the table, so now we have to create a new table with 2 columns of the same data type. For this task, we are executing the following query.

USE sqlserverguides
GO

CREATE TABLE LegalDeptRecords(
  Name VARCHAR(50),
  Department VARCHAR(50)
)
GO

Next, we need to execute the procedure in such a way so that values from the procedure are inserted into the table. For this, we have to use the following query.

USE sqlserverguides
GO

INSERT INTO LegalDeptRecords   
EXEC GetDeptRecords 'Legal'
GO

We inserted the result into a table because we cannot use a procedure directly in a SELECT statement. And now, to get the result, we have to query the table.

table-valued function vs stored procedure in SQL Server
Output

You may like the following SQL server tutorials:

So, in this SQL Server tutorial, we have learned major differences between stored procedures and functions, and we have also covered the following topics.

  • SQL Server stored procedure vs function
  • SQL Server stored procedure vs function performance
  • SQL Server stored procedure vs table-valued function