Chapter 1: Commands

return

Description

Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed.

Syntax

return [integer_expression] [plan "abstract plan"]

Parameters

integer_expression: is the integer value returned by the procedure. Stored procedures can return an integer value to a calling procedure or an application program.

plan "abstract plan": specifies the abstract plan to use to optimize the query. The abstract plan can be a full or partial plan specified in the abstract plan language. Plans can be specified only for optimizable SQL statements, that is, queries that access tables. See Chapter 16, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide: Optimizer and Abstract Plans for more information.

Examples

Example 1

If no user name is given as a parameter, the return command causes the procedure to exit after a message has been sent to the user’s screen. If a user name is given, the names of the rules created by that user in the current database are retrieved from the appropriate system tables:

create procedure findrules @nm varchar(30) = null as 
if @nm is null 
begin 
    print "You must give a user name" 
    return 
end 
else 
begin 
    select sysobjects.name, sysobjects.id,
    sysobjects.uid 
    from sysobjects, master..syslogins 
        where master..syslogins.name = @nm 
        and sysobjects.uid = master..syslogins.suid 
        and sysobjects.type = "R" 
end

Example 2

If the updates cause the average price of business titles to exceed $15, the return command terminates the batch before any more updates are performed on titles:

print "Begin update batch"
update titles
    set price = price + $3
    where title_id = 'BU2075'
update titles
    set price = price + $3
    where title_id = 'BU1111'
if (select avg(price) from titles
      where title_id like 'BU%') > $15
begin
      print "Batch stopped; average price over $15"
    return
end
update titles
    set price = price + $2
      where title_id = 'BU1032'

Example 3

This procedure creates two user-defined status codes: a value of 1 is returned if the contract column contains a 1; a value of 2 is returned for any other condition (for example, a value of 0 on contract or a title_id that did not match a row):

create proc checkcontract @param varchar(11) 
as 
declare @status int 
if (select contract from titles where title_id = @param) = 1 
    return 1 
else 
    return 2

Usage

The return status value can be used in subsequent statements in the batch or procedure that executed the current procedure, but must be given in the form:
```
execute @retval = procedure_name
```
See execute for more information.

Adaptive Server reserves 0 to indicate a successful return, and negative values in the range -1 to -99 to indicate different reasons for failure. If no user-defined return value is provided, the Adaptive Server value is used. User-defined return status values cannot conflict with those reserved by Adaptive Server. Numbers 0 and -1 through -14 are currently in use:

**Table 1-32: Adaptive Server error return values**
Value	Meaning
0	Procedure executed without error
-1	Missing object
-2	Datatype error
-3	Process was chosen as deadlock victim
-4	Permission error
-5	Syntax error
-6	Miscellaneous user error
-7	Resource error, such as out of space
-8	Nonfatal internal problem
-9	System limit was reached
-10	Fatal internal inconsistency
-11	Fatal internal inconsistency
-12	Table or index is corrupt
-13	Database is corrupt
-14	Hardware error

Values -15 to -99 are reserved for future Adaptive Server use.

If more than one error occurs during execution, the status with the highest absolute value is returned. User-defined return values always take precedence over Adaptive Server-supplied return values.
The return command can be used at any point where you want to exit from a batch or procedure. Return is immediate and complete: statements after return are not executed.
A stored procedure cannot return a NULL return status. If a procedure attempts to return a null value, for example, using return @status where @status is NULL, a warning message is generated, and a value in the range of 0 to -14 is returned.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

return permission defaults to all users. No permission is required to use it.