RETURN

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 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  Whether Microsoft® SQL Server™ 2000 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.sid
      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

ALTER PROCEDURE

CREATE PROCEDURE

DECLARE @local_variable

EXECUTE

SET @local_variable