RETURN (T-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 following RETURN are not executed.

Syntax

RETURN [integer_expression]

Arguments

integer_expression

Is the integer value 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, which indicates success; a nonzero value indicates failure.

Remarks

When used with a stored procedure, RETURN cannot return a null value. If a procedure attempts to return a null value (for example, using RETURN @status and @status is NULL), a warning message is generated and a value from 0 through -14 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 Whether Microsoft® SQL Server™ interprets an empty string (NULL) as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server 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.

Examples

A. Return from a procedure

This example shows if no username is given 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 username is given, 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 username'

        RETURN

    END

ELSE

    BEGIN

        SELECT o.name, o.id, o.uid

        FROM sysobjects o INNER JOIN master..syslogins l

            ON o.uid = l.suid

        WHERE l.name = @nm

    END

B. Return status codes

This example checks the state for the specified author’s ID. If the state is California (CA), a status of 1 is returned. Otherwise, 2 is returned for any other condition (a value other than CA for state or an au_id that did not match a row).

CREATE PROCEDURE checkstate @param varchar(11)

AS

IF (SELECT state FROM authors WHERE au_id = @param) = 'CA'

    RETURN 1

ELSE

    RETURN 2

The following examples show the return status from the execution of checkstate. The first shows an author in California; the second, an author not in California; and the third, an invalid author. The @return_status local variable must be declared before it can be used.

DECLARE @return_status int

EXEC @return_status = checkstate '172-32-1176'

SELECT 'Return Status' = @return_status

GO

Here is the result set:

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

Execute the query again, specifying a different author number.

DECLARE @return_status int

EXEC @return_status = checkstate '648-92-1872'

SELECT 'Return Status' = @return_status

GO

Here is the result set:

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

Execute the query again, specifying another author 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