Esat Erkec
Stored procedure output parameters

Using Stored Procedures with Return Values

August 6, 2021 by

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.

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.

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.

Using stored procedure return value

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.

The return value of a stored procedure

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.

Data conversion error of a stored procedure

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.

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

    Stored procedure output parameters

    • 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

    SSMS stored procedure folder

  • Right-click on the stored procedure and select the Execute Stored Procedure menu

    Execute a stored procedure to using Management Studio

  • The required parameters fill in the Execute Procedure window and click OK

    Fill in the parameter list to execute a stored procedure in SSMS.

  • SSMS automatically creates a call script of the procedure and also executes it

    SSMS can automaticly execute a stored procedure

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.

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.

Stored procedure output parameters and NULL values

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.

Warning message of a stored procedure

In order to handle this issue, we can use the ISNULL function before returning the value.

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
38,629 Views