return

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

Examples

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
  • The SAP ASE 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 SAP ASE value is used. User-defined return status values cannot conflict with those reserved by the SAP ASE server. Numbers 0 and -1 through -14 are currently in use:

    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 SAP ASE 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 SAP ASE-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

No permission is required to use return.

Related reference
execute
begin...end
if...else
while