RETURN (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

Transact-SQL syntax conventions

Syntax

RETURN [ integer_expression ]   

Arguments

integer_expression
Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.

Return Types

Optionally returns int.

Note

Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.

Remarks

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = <procedure_name>.

Examples

A. Returning from a procedure

The following example shows if no user name is specified as a parameter when findjobs is executed, RETURN causes the procedure to exit after a message has been sent to the user's screen. If a user name is specified, the names of all objects created by this user in the current database are retrieved from the appropriate system tables.

CREATE PROCEDURE findjobs @nm sysname = NULL  
AS   
IF @nm IS NULL  
    BEGIN  
        PRINT 'You must give a user name'  
        RETURN  
    END  
ELSE  
    BEGIN  
        SELECT o.name, o.id, o.uid  
        FROM sysobjects o INNER JOIN master..syslogins l  
            ON o.uid = l.sid  
        WHERE l.name = @nm  
    END;  

B. Returning status codes

The following example checks the state for the ID of a specified contact. If the state is Washington (WA), a status of 1 is returned. Otherwise, 2 is returned for any other condition (a value other than WA for StateProvince or ContactID that did not match a row).

USE AdventureWorks2022;  
GO  
CREATE PROCEDURE checkstate @param VARCHAR(11)  
AS  
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'  
    RETURN 1  
ELSE  
    RETURN 2;  
GO  

The following examples show the return status from executing checkstate. The first shows a contact in Washington; the second, contact not in Washington; and the third, a contact that is not valid. The @return_status local variable must be declared before it can be used.

DECLARE @return_status INT;  
EXEC @return_status = checkstate '2';  
SELECT 'Return Status' = @return_status;  
GO  

Here's the result set.

Return Status 
 
------------- 
 
1

Execute the query again, specifying a different contact number.

DECLARE @return_status INT;  
EXEC @return_status = checkstate '6';  
SELECT 'Return Status' = @return_status;  
GO  

Here's the result set.

Return Status  
-------------  
 
2

Execute the query again, specifying another contact number.

DECLARE @return_status INT  
EXEC @return_status = checkstate '12345678901';  
SELECT 'Return Status' = @return_status;  
GO  

Here's the result set.

Return Status  
-------------  
 
2

See Also

ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
EXECUTE (Transact-SQL)
SET @local_variable (Transact-SQL)
THROW (Transact-SQL)