RETURN (Transact-SQL)

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

Note

The compatibility-level setting determines whether an empty string (NULL) is interpreted as a single space or as a true empty string. If the compatibility level is less than or equal to 65, SQL Server 2005 interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel (Transact-SQL).

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 AdventureWorks;
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

See Also

Reference

ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
EXECUTE (Transact-SQL)
SET @local\_variable (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance