LEAVE | Teradata Vantage - LEAVE - Advanced SQL Engine - Teradata Database

Breaks execution of a labeled iteration or compound statement and continues execution outside an iteration statement.

• If the LEAVE statement references a label associated with a compound statement, it terminates the execution of that compound statement.

  This action is treated as successful completion of the stored procedure only if the label is associated with the outermost or the only compound statement in the stored procedure.

• If LEAVE references the label of an iteration statement (FOR, LOOP, REPEAT, or WHILE), it breaks the iteration and passes control to the next statement outside the label.
• LEAVE executes the following actions depending on the referenced iteration statement:

  IF LEAVE specifies the label of …	THEN …
  Any statement	LEAVE terminates the execution of its associated iteration statement and any iteration statements nested within.
  FOR statement	the cursor closes before control is transferred to the next statement outside the referenced iteration statement.
  An outer iteration statement containing FOR statement(s)	all open cursors specified in the iteration statement are closed before control transfers to the next statement following the iteration statement.
  The BEGIN END compound statement to which the LEAVE belongs	all open cursors declared in that compound statement are closed and control is transferred to the statement following that compound statement: caller, terminating the procedure. A success or “ok” response is returned to the procedure.

• Any error condition encountered while closing the cursor is reflected in the status variables.

  Example:

  SQLCODE=7600, SQLSTATE=’T7600’, ACTIVITY_COUNT=0.

ANSI Compliance

LEAVE is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures only.

Syntax

LEAVE label_name ;

Syntax Elements

label_name

The name of the label for the iteration statement or BEGIN END block to be terminated by the LEAVE.

Usage Notes

• You can specify LEAVE anywhere within the scope of its referred label.
• The label must be associated with either of the following: an iteration statement or the BEGIN END compound statement in which you embed the LEAVE statement.

Example: Terminating the Execution of a Stored Procedure

The following example illustrate a valid use of LEAVE to terminate the execution of a stored procedure:

CREATE PROCEDURE spSample()
SPLABEL:
BEGIN
   DECLARE vCount INTEGER DEFAULT 0;
   WHILE vCount <= 10
   DO
      UPDATE table_1
       SET table_1.column_1 = vCount
       WHERE table_1.column_2 > 10;
      IF ACTIVITY_COUNT = 0 THEN
        LEAVE SPLABEL;
   END IF;
 END WHILE;
END;

Example: Using LEAVE with an Iteration Statement

The following example illustrates a valid use of LEAVE with an iteration statement:

LABEL1:
WHILE i < 10 
DO
   UPDATE table_1
    SET table_1.column_1 = i
    WHERE table_1.column_2 > 10;
  IF ACTIVITY_COUNT > 1 THEN
       LEAVE LABEL1;
  END IF;
    SET i = i+1;
END WHILE LABEL1;