TechNet
Export (0) Print
Expand All

RETURN (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions

  
RETURN [ integer_expression ]   

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

Optionally returns int.

System_CAPS_ICON_note.jpg Note


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

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>.

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 AdventureWorks2012;  
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 is 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 is 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 is the result set.

Return Status

-------------

2

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

Community Additions

ADD
Show:
© 2016 Microsoft