SQL RETURN and SQL OUTPUT clause in Stored Procedures

Problem

SQL Server database professionals use stored procedures for reuse and also to improve performance. One item a SQL Server stored procedure does when it runs, is to output a return value for the @return_value parameter. In this tutorial, we will explore the value returned by @return_value and how you can configure it for your needs.

Solution

SQL Stored Procedures Benefits

A stored procedure is commonly used in SQL Server databases and provides the following benefits:

Performance: In the initial run, the SQL Server query optimizer creates the execution plan and caches this plan. Subsequent executions use the cached stored procedure which can reduce the overall execution time.
Code reuse: You can execute the stored procedure N number of times and use the same exact code in multiple places.
Easy to maintain: Suppose you use the stored procedure in many parts of your code and there is a need for a change. Using the stored procedure, you can alter the code in one place and this code is now updated wherever it is called.
Security: You can simplify managing the security of different objects using the stored procedure. It can prevent direct access to tables and you can provide access to the stored procedure instead of the tables that are used in the stored procedure.
- Note: You can refer to SQL Server Security with Stored Procedures and Views for more details.

Examples of the SQL RETURN Clause in Stored Procedures

To understand the problem, let’s create a stored procedure with the following script. For this demo, I am using the Azure SQL Database sample schema [SalesLT]. [Customer].

CREATE PROCEDURE [dbo].[GetCustomerEmail]
(@CustID AS int)
AS
BEGIN
    SELECT EmailAddress FROM [SalesLT].[Customer] 
    WHERE CustomerID=@CustID
END
GO

In SQL Server Management Studio (SSMS), expand Programmability > Stored Procedures, right click a stored procedure and select Execute Stored Procedure.

In the execute procedure page, enter the parameter @CustID value as 10 and click OK.

It returns the following T-SQL statement with a variable @return_value. The default @return_value data type is an integer (int).

DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetCustomerEmail]
        @CustID = 10
 
SELECT 'Return Value' = @return_value
GO

When executing the stored procedure, it gives the following output. The first output gives the result of the select statement inside the [GetCustomerEmail] stored procedure and the second result set returns the stored procedure return value.

By default, if a stored procedure returns a value of 0, it means the execution is successful.

The Return Codes or values are commonly used in the control-of-flow blocks within procedures. You can change these return values as per your requirements.

For example, let’s alter this stored procedure to return the number of records returned by the SELECT statement. In this query, we specified a value for the RETURN clause.

CREATE OR ALTER PROCEDURE [dbo].[GetCustomerEmail_2]
AS
BEGIN
   DECLARE @Recordcount int
   
   SELECT @Recordcount = count(*) FROM [SalesLT].[Customer] 
    
   RETURN @Recordcount
END
GO

If we execute this stored procedure, it returns the total number of records, i.e. 847 in the return value.

Examples of SQL Return Codes in Stored Procedures

In the previous example, we saw the primary use of the Return values in the SQL Server stored procedure. We can use these values to define the stored procedure logic and error handling.

Let’s create another stored procedure with the following script.

CREATE OR ALTER PROCEDURE [dbo].[GetCustomerEmail] (@CustID int)
AS
DECLARE @Recordcount int 
DECLARE @valid bit 
DECLARE @Emailaddress nvarchar(200)
--SELECT @Recordcount= count(*) FROM [SalesLT].[Customer] WHERE customerID=@CustID
If(@CustID IS NULL)
        RETURN 1
If(Select count(*) FROM [SalesLT].[Customer] WHERE customerID=@CustID) > 0
BEGIN
    SELECT @valid= dbo.ChkValidEmail(Emailaddress) FROM [SalesLT].[Customer] WHERE customerID=@CustID
    If(@valid=0)
        RETURN 3;
        Else
    SELECT Emailaddress FROM [SalesLT].[Customer] WHERE customerID=@CustID
END
    ELSE
    RETURN 2

This script has the following logic:

It defines a parameter @CustID with a default value of NULL.
If you execute the stored procedure with the default value, i.e. NULL, it returns the value 1 (RETURN 1)
Suppose there is no record for the specific customer ID in the [SalesLT].[Customer] table this returns the value 2 (RETURN 2).
It uses the function dbo.chkValidEmail() to check whether the email address available for the customer is valid or not.
- If an email address is invalid, the SP returns value 3 (RETURN 3)
- If an email address is correct, it returns the customer email address.

Note: For this tip, you can create the function dbo.chkValidEmail() from Valid Email Address Check with TSQL. The dbo.chkValidEmail() returns value 1 and 0 for valid and invalid email address respectively.

Once we execute the stored procedure, it gives the RETURN codes as defined in the SP logic. Therefore, the following T-SQL uses these return codes to interpret the result accordingly.

DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetCustomerEmail]
        @CustID = 10
 
IF @return_value = 0  
BEGIN  
   PRINT 'Procedure executed successfully'  
END  
ELSE IF @return_value = 1  
   PRINT 'ERROR: NULL is not a valid value for the Customer id.'  
ELSE IF @return_value = 2   
   PRINT 'ERROR: No Record exists for the customer ID you specified.'  
ELSE IF @return_value = 3  
   PRINT 'ERROR: Invalid Email address.'  
GO

Let’s test the stored procedure output with a few scenarios.

Specify a Valid Value for the Customer ID

In this scenario, we execute the stored procure with a valid customer id. In this case, the stored procedure returns a value of 0. Therefore, you get the customer email address and the specified message – “Procedure executed successfully”.

To view the print message, click on the Messages tab of the output.

Run the Stored Procedure with a Default Value, i.e., NULL

If we run the stored procedure with default value of NULL, it returns 1 and prints the message that we specified for the value 1.

You get a similar message even if you specify an explicit NULL value.

Invalid Format of Email Address

For this example, we update the customer email address to make it invalid so we can test the stored procedure for the email address check.

UPDATE [SalesLT].[Customer] 
SET EmailAddress = 'orlando0adventure-works.com' 
WHERE customerid=1

Now, run the stored procedure for CustID = 1 and see what it returns in the output. The stored procedure returns value 3 for an invalid email address, and therefore, the execution script prints the message accordingly.

SQL Output Clause in Stored Procedures

You can specify the OUTPUT keyword in the procedure definition for a parameter. If you specify it, the stored procedure returns the value of the current parameter when the stored procedure executes. You can also save the output value in a variable as well.

For example, in the below stored procedure definition, we use two parameters.

@FirstName: It is the input parameter based on which we want to filter data. In our case, the SP returns rows where the customer’s first name matches the input of @FirstName.
@CountRecords: This parameter has the OUTPUT keyword, and it will capture the stored procedure output value we specified using SELECT @CountRecords = @@ROWCOUNT. Here, the @@ROWCOUNT is a system variable, and it returns the number of rows read by the previous statement.

CREATE OR ALTER PROCEDURE [dbo].[GetCustomerEmail]
(  @FirstName AS varchar(100),
   @CountRecords int OUTPUT
)
AS
BEGIN
    SELECT FirstName, MiddleName, Lastname, EmailAddress 
    FROM [SalesLT].[Customer] 
    WHERE FirstName = @FirstName
    
    SELECT @CountRecords = @@ROWCOUNT;
END
GO

Now, specify the input of the @FirstName to run the stored procedure. In the below script, we capture the stored procedure parameter @CountRecords output in a variable @CountRecords. You also need to specify the OUTPUT clause to get the parameter value from the stored procedure.

DECLARE @Count varchar(10)
DECLARE @SearchFirstName varchar(200) = 'David'
EXEC [dbo].[GetCustomerEmail] @FirstName=@SearchFirstName, @CountRecords=@Count OUTPUT
PRINT 'Total number of customer having First Name as'+ ' ' + @SearchFirstName+ '  '+'are:'+  @Count;  
GO

The stored procedure returns the data and the custom message, as shown below.

Next Steps

Read more about Returning Data from SQL Server Stored Procedures.
Read more about stored procedures.

Resources

Community

Subscribe