SQL Server Stored Procedure Output Parameters

SQL Server Stored Procedure Output Parameters

In this article, I am going to discuss the SQL Server Stored Procedure Output Parameters with some examples. Please read our last article before proceeding to this article where we discussed the SQL Server Stored Procedure Input Parameters with some examples.

As we already discussed the parameters of a stored procedure in SQL Server can be of two types

  1. Input parameters
  2. Output parameters

The Input parameters of SQL Server Stored Procedure are used for bringing the values into the procedure for execution. On the other hand, the SQL Server Stored Procedure output parameters are used for carrying a value out of the procedure after its execution.

Note: We only require assigning a value to the output parameter inside the procedure so that procedure will send that value out at the end of procedure execution.

The Output parameter in SQL Server can be declared either by using the OUT or OUTPUT keyword

Example1: Let’s create a simple stored procedure to understand the SQL Server Stored Procedure Output Parameters
CREATE PROCEDURE spGetResult
  @No1 INT,
  @No2 INT,
  @Result INT OUTPUT
AS
BEGIN
  SET @Result = @No1 + @No2
END

The above SQL Server Stored Procedure takes 3 parameters. The @No1 and @No2 are input parameters by default whereas the @Result is the output parameter. The Parameter which is created using the OUT or OUTPUT keyword is called as the output parameter in SQL Server.

To execute a procedure with output parameter, First, we need to declare a variable, then we need to pass that variable while calling the procedure by specifying the type as output as shown below.

-- To Execute Procedure
DECLARE @Result INT
EXECUTE spGetResult 10, 20, @Result OUT
PRINT @Result

When we execute the above code it will print 30.

Let’s see more example for better understanding of SQL Server stored procedure output parameters.

We are going to use the following Employee table to understand the SQL Server Stored Procedure Output Parameters.

SQL Server Stored Procedure Output Parameters

Please use below SQL Script to create and populate the Employee table.

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO
Example2: Create a stored procedure to get the total number of employee in the Employee table by Gender.

As we already discussed to create a Stored Procedure in SQL Server with an output parameter, we need to use the keyword OUT or OUTPUT. In the following Stored Procedure, the @EmployeeCount is an output parameter as we specified the parameter with the OUTPUT keyword. 

CREATE PROCEDURE spGetEmployeeCountByGender
  @Gender VARCHAR(30),
  @EmployeeCount INT OUTPUT
AS
BEGIN
  SELECT @EmployeeCount = COUNT(ID)
  FROM	   Employee
  WHER     Gender = @Gender
END

Let’s see the different ways to execute the above SQL Server Stored Procedure with the output parameter.

Step1: First declare a variable of the same data type as that of the output parameter. Here we have declared the @EmployeeTotal integer variable. 

Step2: Then we need to pass the @EmployeeTotal variable to the stored procedure. We have to specify the variable with the OUTPUT keyword. If we don’t specify the OUTPUT keyword, the variable will be NULL.

Step3. Execute

Way1: Allowed
DECLARE @EmployeeTotal INT
EXECUTE spGetEmployeeCountByGender 'Male', @EmployeeTotal OUTPUT
PRINT @EmployeeTotal

NOTE: If we don’t specify the output keyword when executing the stored procedure then the @EmployeeTotal value will be null. For example, see the following

DECLARE @EmployeeTotal INT
EXECUTE spGetEmployeeCountByGender 'Male', @EmployeeTotal
PRINT @EmployeeTotal
Whether it will print null or not check the following:
DECLARE @EmployeeTotal INT
EXECUTE spGetEmployeeCountByGender'Male', @EmployeeTotal
IF (@EmployeeTotal IS NULL)
  PRINT '@EmployeeTotal IS NULL'
ELSE
  PRINT '@EmployeeTotal IS NOT NULL'

Way2: Not Allowed

DECLARE @EmployeeTotal INT
EXECUTE spGetEmployeeCountByGender @EmployeeTotal OUTPUT, 'Male'
PRINT @EmployeeTotal
Way3: Allowed

We can pass the parameters in any order when we use the parameter names. Here, we are first passing the output parameter and then the input @Gender parameter.

DECLARE @EmployeeTotal INT
EXECUTE spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUTPUT, @Gender ='Male'
PRINT @EmployeeTotal
What are the advantages of using a Stored Procedure?

This is one of the most frequently asked interview question in SQL Server. Let discuss this question in details.

Execution Plan Retention and Reusability

As there is no unnecessary compilation of queries this will reduces the burden on database (when we send a query to a SQL Server three things happen in order, 1st it checks the syntax of that query, 2nd it compiles that query, 3rd it generates an execution plan) as response user will get a quick response. Let’s get into more details.

The Stored Procedures are pre-compiled and their execution plan is cached and used again when the same stored procedure is executed again. Although ad-hoc queries also create and reuse plan, the plan is reused only when the query is the textual match and the datatypes are matching with the previous call. Any changes in the datatype or you have an extra space in the query then, a new plan is created.

Reduces the Network Traffic

The Stored Procedure reduces the network traffic. When we execute a stored procedure we need to send the procedure name and parameters so only these things are passed on the network but if we are not using the stored procedure then we need to write the ad-hoc queries and we need to execute them which may contain many numbers of lines. So the stored procedure reduces the network traffic as a result performance of the application increase.

Code Reusability and Better Maintainability

Multiple applications can use the same stored procedure. The different applications which want similar kind of data then they can use the same stored procedure. The advantage is that if we want to change the stored procedure then we need to change it in one place that will affect to all the application that uses it whereas if it is inline SQL query and if we have to use it in multiple applications, then we end up with multiple copies of the same inline SQL query, and if the logic has to change, then we have to change the logic at all the places, which makes it harder maintaining inline SQL. So, the stored procedure provides code reusability and maintainability.

Better Security 

By granting permission to the underlying database the user can do everything. He can view all the records as well as he can also modify the records. But if we want to restrict the user only to view the records then we need to grant only for that stored procedure which will display the records. In that way, we achieve better security with a stored procedure.

Using stored procedure we can also avoid the SQL Injection attack.

What is an execution plan?

An execution plan is nothing but for the query to retrieve the data what is the best possible way available. This depends on the indexes that available on the SQL Server to help that query. Based on those it generates the execution plan and then it executes the query.

In the next article, I will discuss the SQL Server Stored Procedure Return Value with examples.

SUMMARY

In this article, I try to explain the SQL Server Stored Procedure Output Parameters step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *