In this article, we will learn how we can use a Stored Procedure with return values with all details.
Introduction
In the modular programming approach, the independent parts of the codes can be divided into subprograms. Based on this idea, stored procedures can be used to create subprograms in SQL Server, and we can make a straightforward definition for them as follows: SQL Server stored procedure is the reusable set of SQL commands that are stored in the database. After this definition, the first question that comes to your mind will be “Why do we use stored procedures in SQL Server? The following reasons are the most basic principles as to why we choose them:
- The stored procedure accepts input parameters and also stored procedures return values after the execution
- Reduce the network traffic because a set of SQL statements can be encapsulated into it
- Offers a faster execution because the cached execution plan reusability reduces the server overhead
- Offers better security because they help to prevent SQL injection attacks and allows us to encrypt their source codes
Pre-Requirments
In this article, we will use a sample table and the following query will help to create this table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE SampleDepartments ( Id INT PRIMARY KEY NOT NULL IDENTITY(1,1), DepartmentName VARCHAR(25) NOT NULL, RecordDate DATETIME , AverageSalary MONEY ) GO INSERT INTO SampleDepartments (DepartmentName,RecordDate, AverageSalary) VALUES ('Human Resource' , '2012-01-01 12:00:21.000','15000' ), ('Online Sales','2005-01-01 13:12:16.000','18000'), ( 'Information Technology','2016-07-01 11:01:12.000','20000') |
Return Value in SQL Server Stored Procedure
In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error. The following query will create a very simple stored procedure to get departments’ information. Let’s understand this concept better with an example.
|
1 2 3 4 5 |
CREATE PROCEDURE GetDepartmentInfo @DepartmentName AS VARCHAR(25) AS SELECT DepartmentName , RecordDate ,AverageSalary FROM SampleDepartments WHERE DepartmentName=@DepartmentName |
When we execute the created stored procedure to use the following query, we will see two result sets. The first result set shows the result set of the select statement inside the code of the GetDepartmentInfo procedure. The second result set displays the stored procedure return value.
|
1 2 3 4 5 |
DECLARE @return_value int EXEC @return_value = [dbo].[GetDepartmentInfo] @DepartmentName = 'Human Resource' SELECT 'Return Value' = @return_value GO |
Changing Stored Procedure Return Value
We can change the return value inside the procedure code using the RETURN command. For example, the following stored procedure will return the record number of the SampleDepartments table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE GetDepartmentRecord AS DECLARE @DepartmentRow AS INT SELECT @DepartmentRow=COUNT(*) FROM SampleDepartments RETURN @DepartmentRow GO DECLARE @return_value int EXEC @return_value = GetDepartmentRecord SELECT 'Return Value' = @return_value GO |
As we can see, the GetDepartmentRecord stored procedure return value has displayed the total row number of the SampleDepartments table because we assigned this value to it inside the procedure. The stored procedure return value data type can only be an integer and can not be changed. Therefore, we can not assign any data type other than an integer value, otherwise, the assigned value is converted implicitly into an integer or get a data type conversion error. For example, the following procedure will assign which department gets the maximum salary to the return value, but executing the stored procedure will return an error due to data type conversion.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE GetDepartmentMaxSalary AS RETURN(SELECT TOP 1 DepartmentName FROM SampleDepartments ORDER BY AverageSalary DESC) Now, we will execute the GetDepartmentMaxSalary stored procedure. DECLARE @return_value int EXEC @return_value = [GetDepartmentMaxSalary] SELECT 'Return Value' = @return_value |
Stored procedure OUTPUT parameters
We can define output parameters instead of the return value in the stored procedure and can assign several data types to it. For example, the following procedure returns all department names and also returns the maximum record date with the help of the @MaxRecordDate output parameter.
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE GetDepartmentSalaryList @Salary MONEY, @MaximumRecordDate DATETIME OUTPUT AS SELECT @MaximumRecordDate=MAX(RecordDate) FROM SampleDepartments SELECT * FROM SampleDepartments WHERE AverageSalary <@Salary |
In the creation script of the GetDepartmentSalaryList procedure, we have defined an output parameter and then we assigned the maximum record date inside the code of the stored procedure. In order to call a stored procedure with output parameters
- Explicitly declare a variable to hold the values returned by the output parameters
-
Assign output parameter to this variable in the stored procedure call
12345DECLARE @LastRecordDate datetimeEXEC [GetDepartmentSalaryList]@Salary = 30000,@MaximumRecordDate = @LastRecordDate OUTPUTSELECT @LastRecordDate AS MaxiumumRecordDate
- Tip: SQL Server Management Studio (SSMS) enables us to create an execution query of a stored procedure in just only a few clicks
-
Find the underlined procedure under the Stored Procedure folder
-
Right-click on the stored procedure and select the Execute Stored Procedure menu
-
The required parameters fill in the Execute Procedure window and click OK
-
SSMS automatically creates a call script of the procedure and also executes it
Try to return a NULL value in a stored procedure
In some cases, a NULL value can be assigned to the stored procedure return value when changing it. At this point, SQL Server automatically converts NULL values into 0. The example below describes a scenario similar to this.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE GetDepartmentSalaryRange @Salary MONEY AS DECLARE @ReturnSalary AS INT IF @Salary < 25000 BEGIN SELECT @ReturnSalary=COUNT(*) FROM SampleDepartments WHERE AverageSalary <=@Salary END RETURN @ReturnSalary |
When we try to execute the GetDepartmentSalaryRange procedure for any parameter which is greater than 25000, the code logic does not enter the IF statement. In this case, no value can be assigned to the @ReturnSalary variable, but the return value shows to be 0, but the expected value was NULL.
|
1 2 3 4 |
DECLARE @return_value int EXEC @return_value = [dbo].[GetDepartmentSalaryRange] @Salary = 30000 SELECT 'Return Value' = @return_value |
However, when we click the Messages tab of the result panel, we can see a warning message “The ‘GetDepartmentSalaryRange’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead”. Actually, an explanation of the message is explains everything understandably. We have declared a variable inside the scope of the procedure and we have not assigned any value to the variable before we return it in the procedure that’s why SQL Server implicitly converts NULL to 0.
In order to handle this issue, we can use the ISNULL function before returning the value.
|
1 2 3 4 5 6 7 8 9 10 11 |
ALTER PROCEDURE GetDepartmentSalaryRange @Salary MONEY AS DECLARE @ReturnSalary AS INT IF @Salary < 25000 BEGIN SELECT @ReturnSalary=COUNT(*) FROM SampleDepartments WHERE AverageSalary <=@Salary END RETURN ISNULL(@ReturnSalary,0) |
Conclusion
In this article, we have learned to use stored procedure return values and also have learned how to use a stored procedure with an output parameter.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023