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

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

RESIGNAL resignals or invokes a condition from a handler declaration. The RESIGNAL statement can be specified explicitly only in a handler declaration.

Invocation

Executable.

Stored procedures only.

Syntax

RESIGNAL [ condition_name | SQLSTATE [VALUE] SQLSTATE_code ]
  [ SET condition_information_item = value ] ;

where:

condition_name
The name of a variable declared to identify a condition within an SQL stored procedure.
If condition_name specifies a condition that corresponds to an SQLSTATE value, the value of that SQLSTATE is assigned to RETURNED_SQLSTATE in the Condition Area.
SQLSTATE [VALUE] SQLSTATE_code
The value for an SQLSTATE to be assigned to RETURNED_SQLSTATE in the Condition Area.
value
A text or numeric value to be assigned to the specified condition information name.
condition_information_item
One of the following field names from the Condition Area of the Diagnostics Area as shown in the following table.
CLASS_ORIGIN
The identification of the naming authority that defined the class value of RETURNED_SQLSTATE.
The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata class extension to the SQL:2011 standard.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
CONDITION_IDENTIFIER
The condition name specified in a SIGNAL or RESIGNAL statement.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
CONDITION_NUMBER
Takes values from 1 to 16, where 16 is the maximum number of conditions that can be stored in the diagnostics area.
Data type: INTEGER
Default: 0
MESSAGE_LENGTH
The length of MESSAGE_TEXT in characters.
Data type: INTEGER
Default: 0
MESSAGE_TEXT
The text of the Error or Warning message returned by the previous SQL statement execution or a message specified in a SIGNAL or RESIGNAL statement as signal information.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null
RETURNED_SQLSTATE
The SQLSTATE value returned by the previous SQL statement, the SQLSTATE value specified in a SIGNAL or RESIGNAL statement, or the SQLSTATE value associated with the condition name if a condition name is specified in a SIGNAL or RESIGNAL statement.
Data type: CHARACTER(5) CHARACTER SET LATIN
Default: null
SUBCLASS_ORIGIN
The identification of the naming authority that defined the subclass value of RETURNED_SQLSTATE.
The value must be ISO 9075 if the class value is defined in the ANSI/ISO SQL:2011 standard or Teradata if the class value is a Teradata subclass extension to the SQL:2011 standard.
Data type: VARCHAR(128) CHARACTER SET UNICODE
Default: null

Usage Notes

RESIGNAL always propagates a condition outward. When a RESIGNAL statement is submitted from a handler action, the outer containing compound statements are searched for the most appropriate condition handler declaration. A RESIGNAL statement with a signal value does not clear the diagnostics area.

Rules

The following rules apply to RESIGNAL:
  • If a RESIGNAL statement is used outside a condition handler, the request aborts during stored procedure compilation and returns an error to the requestor.
  • If a condition name is specified in a RESIGNAL statement the condition name must be declared in the scope that applies to the handler containing the RESIGNAL statement. Otherwise, the request aborts during stored procedure compilation and returns an error to the requestor.
  • The usage of a condition name in a RESIGNAL statement is equivalent to using the SQLSTATE value to which the condition name corresponds if the condition name is associated with an SQLSTATE value.
  • If more than one condition declaration is specified for the same condition name, the one that is the most local to the scope of the compound statement containing the RESIGNAL statement is used.
  • If a signal value is specified in a RESIGNAL statement, the Statement Area is modified with the details of the RESIGNAL statement and the existing Condition Areas, if any, are stacked such that the n th Condition Area is placed at the position of the (n+1)th condition area in the Diagnostics Area. If signal information is specified in the RESIGNAL statement, Condition Area 1 is modified with the details given in the signal information before pushing down the existing Condition Areas. A new Condition Area 1 corresponding to the signal value is added to the Diagnostics Area.
    • If the signal value is a condition name, CONDITION_IDENTIFIER in Condition Area 1 is set to contain condition name. If condition name is associated with an SQLSTATE value, RETURNED_SQLSTATE in Condition Area 1 is set to contain this SQLSTATE value.
    • Otherwise, if the signal value is an SQLSTATE value, RETURNED_SQLSTATE in Condition Area 1 is set to contain this SQLSTATE value.
    • A handler is searched in a containing outer compound statement to handle the condition raised by the RESIGNAL statement.
  • If there is no containing outer compound statement that has a handler to handle the condition raised by the RESIGNAL statement, one of the following happens:
    • An exception: The stored procedure exits with the exception condition raised by the RESIGNAL statement.
    • A completion condition: The execution continues from the statement following the RESIGNAL statement in the handler containing the RESIGNAL statement.
    • A user-defined exception condition: An exception that sets SQLCODE to 7603 and SQLSTATE to '45000' is raised.
  • If there is a handler in a containing outer compound statement that can handle the condition raised by the RESIGNAL statement, one of the following happens:
    • CONTINUE handler: Stored procedure execution continues from the statement following the statement that invoked the handler containing the RESIGNAL statement.
    • EXIT handler: Stored procedure execution continues from the statement following the END compound statement containing the handler whose action clause has just completed successfully.
  • If a RESIGNAL statement is submitted without any signal value, one of the following things happens:
    • The Diagnostics Area is cleared and its original contents, with which the handler containing the RESIGNAL statement was invoked, are restored in the Diagnostics Area.

      The CONDITION_IDENTIFIER and RETURNED_SQLSTATE in Condition Area 1 reflect the original condition with which the handler was invoked.

    • If signal information is specified, Condition Area 1 is modified with the details given in the signal information specification of the RESIGNAL statement.
    • The original condition with which the handler was invoked is propagated outward and the containing outer compound statements are searched for a handler for this condition.
  • If a RESIGNAL statement uses a user-defined condition, and no handler declaration is defined to handle the condition in the scope of the compound statement containing the RESIGNAL statement, Teradata Database reports a warning during compilation.

Rules specified in “SIGNAL” for signal information also apply to the signal information in a RESIGNAL statement.

Contents of the Diagnostics Area

The following table specifies the contents of the Statement Area after the execution of a RESIGNAL statement specified with a signal value:

Field Value
COMMAND_FUNCTION RESIGNAL
COMMAND_FUNCTION_CODE 91
NUMBER If NUMBER < 16, increment by 1.

If NUMBER   16, value not changed.

MORE Y if the value of NUMBER is changed.

N if the value of NUMBER is not changed.

ROW_COUNT 0
TRANSACTION_ACTIVE 0 if no transaction is active.

1 if a transaction is active.

The following table specifies the contents of Condition Area 1 after the execution of a RESIGNAL statement specified with signal information. If a signal value is also specified in the RESIGNAL statement, this Condition Area is pushed to condition number 2.

Field Value
CLASS_ORIGIN If CLASS_ORIGIN is specified in signal information, this field contains the specified value.

Otherwise, the existing value is retained.

CONDITION_IDENTIFIER The existing value is retained because this field cannot be modified.
CONDITION_NUMBER The existing value is retained because this field cannot be modified.
MESSAGE_TEXT The value specified for signal information in a RESIGNAL statement.

Otherwise, the existing value is retained.

MESSAGE_LENGTH The length of the MESSAGE_TEXT

If RESIGNAL does not specify a message text value, then this field is set to 0.

RETURNED_SQLSTATE The existing value is retained because this field cannot be modified.
SUBCLASS_ORIGIN If SUBCLASS_ORIGIN is specified in signal information, this field contains the specified value.

Otherwise, the existing value is retained.

The following table specifies the contents of Condition Area 1 after the execution of a RESIGNAL statement specified with a signal value:

Field Value
CLASS_ORIGIN If the RESIGNAL statement specifies:
  • The SQLSTATE value associated with the condition specified in the RESIGNAL statement
  • The SQLSTATE value specified in the RESIGNAL statement:
then the content of CLASS_ORIGIN is determined as follows
  • If the class value is defined by the ANSI/ISO SQL standard, then CLASS_ORIGIN is ISO 9075.
  • If the class value is defined by Teradata, then CLASS_ORIGIN is Teradata.

Data type: VARCHAR(128) CHARACTER SET UNICODE.

If the RESIGNAL statement specifies a user-defined condition, CLASS_ORIGIN contains a null.

CONDITION_IDENTIFIER The condition name specified in the RESIGNAL statement.

Data type: VARCHAR(128) CHARACTER SET UNICODE.

If no condition name is specified, then CONDITION_IDENTIFIER contains a null.

CONDITION_NUMBER 1

Data type: INTEGER

MESSAGE_TEXT null

Data type: VARCHAR(128) CHARACTER SET UNICODE

MESSAGE_LENGTH 0

Data type: INTEGER

RETURNED_SQLSTATE One of the following:
  • The SQLSTATE value associated with the condition specified in the RESIGNAL statement
  • The SQLSTATE value specified in the RESIGNAL statement
  • Null

Data type: CHARACTER(5) CHARACTER SET LATIN

SUBCLASS_ORIGIN If the RESIGNAL statement specifies:
  • The SQLSTATE value associated with the condition specified in the RESIGNAL statement
  • The SQLSTATE value specified in the RESIGNAL statement
then the content of SUBCLASS_ORIGIN is determined as follows:
  • If the class value is defined by the ANSI/ISO SQL standard, then CLASS_ORIGIN is ISO 9075.
  • If the class value is defined by Teradata, then CLASS_ORIGIN is Teradata.
Data type: VARCHAR(128) CHARACTER SET UNICODE.

If the RESIGNAL statement specifies a user-defined condition, SUBCLASS_ORIGIN contains a null.

After the execution of RESIGNAL statement that does not specify a signal value, Teradata Database sets the contents of the Diagnostics Area as follows:
  • The Statement Area is set to the contents of the Statement Area of the Diagnostics Area with which the handler containing the RESIGNAL statement was invoked.
  • The Condition Areas are set to the Condition Areas of the Diagnostics Area with which the handler containing the RESIGNAL statement was invoked.

Example: SET Statement Exceptions

At runtime in this example, the last SET statement in the procedure definition raises an exception that returns SQLCODE 2802 and SQLSTATE '22012'.

The CONTINUE handler defined to handle SQLSTATE '22012' is invoked and the RESIGNAL statement is executed.

Because the condition out_of_range is associated with SQLSTATE '22003', SQLSTATE '22003' is propagated to compound statement cs1 and the EXIT handler is invoked.

After successfully executing the handler action statements, control exits cs1 and the stored procedure terminates successfully.

CREATE PROCEDURE resignalsp3 (INOUT IOParam INTEGER,
                              OUT   OParam INTEGER)
cs1: BEGIN
    DECLARE out_of_range CONDITION FOR SQLSTATE '22003';
    DECLARE EXIT HANDLER FOR SQLSTATE '22003'
        SET OParam = 0;
    cs2: BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE  '22012'
             RESIGNAL out_of_range;
        SET IOParam = 0;
        SET OParam = 20 / IOParam;
    END cs2;
END cs1;
BTEQ> CALL resignalsp3 (0, OParam);

Example: The CONTINUE Handler and the RESIGNAL Statement

The following example illustrates the return point when a CONTINUE handler in an outer compound statement handles the condition raised by a RESIGNAL statement in the inner compound statement.

At runtime, the SET statement raises exception SQLSTATE '22012'.

The CONTINUE handler is invoked and the RESIGNAL statement is executed as part of the handler action statements.

The RESIGNAL statement references the condition declared in cs2 because the condition declaration is more local to the scope of the compound statement containing the RESIGNAL statement.

Because Condition1 is associated with SQLSTATE '21000', the exception condition is propagated to the containing outer compound statement cs1 and the CONTINUE handler for SQLSTATE '21000' is invoked.

After successfully executing the handler action statement, control returns to the statement following the last SET statement in the procedure definition.

CREATE PROCEDURE resignalsp5(INOUT IOParam INTEGER,
                             OUT   OParam INTEGER)
cs1:BEGIN
    DECLARE Condition1 CONDITION FOR SQLSTATE '22012';
    DECLARE EXIT HANDLER FOR Condition1
            SET OParam = 0;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '21000'
            SET OParam = 100;
    cs2:BEGIN
        DECLARE Condition1 CONDITION FOR SQLSTATE '21000';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
        BEGIN
           RESIGNAL Condition1;
           …
        END;
        SET IOParam = 0;
        SET OParam = 20 / IOParam;
        …
    END cs2;
END cs1;
BTEQ> CALL resignalsp5 (0, OParam);

Example: Using a Signal Value and Signal Information in a RESIGNAL Statement

The following example illustrates the usage of a signal value and signal information in a RESIGNAL statement.

During the stored procedure execution, the RESIGNAL statement updates MESSAGE_TEXT in Condition Area 1 with 'Sampling error' and MESSAGE_LENGTH is implicitly set to 14.

Because a signal value is specified in the RESIGNAL statement, the existing Condition Areas of the diagnostics area are pushed down and a new Condition Area is added in the diagnostics area with RETURNED_SQLSTATE as 'T7473' and CONDITION_NUMBER as 1.

CREATE PROCEDURE setsignalsp3(OUT OPar CHAR(100))
cs1: BEGIN
    …
    cs2: BEGIN
      DECLARE samp_error CONDITION;
      DECLARE CONTINUE HANDLER FOR samp_error
       RESIGNAL SQLSTATE 'T7473'
        SET MESSAGE_TEXT = 'Sampling error';
       …
      SIGNAL samp_error;
    …
    END cs2;
END cs1;
BTEQ> .COMPILE FILE setsignalsp3.spl
BTEQ> CALL setsignalsp3(OPar);

The following example illustrates how to propagate the original exception outwards from a handler.Example: Propagating the Original Exception Outwards From a Handler

During stored procedure execution, the last INSERT statement in the procedure definition raises a duplicate row exception, and the handler declared for SQLSTATE ‘23505’ is invoked.

The handler action statement that inserts SQLSTATE, CURRENT_TIMESTAMP, ‘spSample1’, and ‘Failed to Insert record’ results in another exception with SQLSTATE '42000' and activates the generic SQLEXCEPTION handler.

The handler issues a RESIGNAL statement with the original exception that the handler was supposed to handle.

The Diagnostics Area is cleared and restored to the original state at the time the handler was invoked. This causes original exception SQLSTATE '42000' to be restored in Condition Area 1.

Because signal information is specified in the RESIGNAL statement, MESSAGE_TEXT in Condition Area 1 is modified with 'Table does not exist'.

MESSAGE_LENGTH is implicitly set to 20. The initial condition handler in the procedure definition is then invoked to handle exception SQLSTATE '42000'.

After the successful completion of the handler action statements, control returns to the statement following the INSERT handler action statement.

CREATE PROCEDURE spSample3(IN pName     CHAR(30),
                           IN pAmt      INTEGER,
                              Osqlstate CHAR(5),
                              Omsg      CHAR(30))
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
     GET DIAGNOSTICS EXCEPTION 1
          Osqlstate = RETURNED_SQLSTATE, Omsg = MESSAGE_TEXT;
  L1:BEGIN
     DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
     BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION
          BEGIN
           DELETE FROM tab1;
           RESIGNAL SET MESSAGE_TEXT = 'Table does not exist';
          END;
          INSERT INTO Proc_Error_Tbl
          VALUES (SQLSTATE, CURRENT_TIMESTAMP, 'spSample1',
                  'Failed to Insert record');
          …
     END;
     INSERT INTO tab1 VALUES (pName, pAmt);
     INSERT INTO tab1 VALUES (pName, pAmt); -- Duplicate row error
     …
  END L1;
  …
END;
BTEQ> .COMPILE FILE nblk3.spl
BTEQ> CREATE SET TABLE tab1(c1 CHAR(30), c2 INTEGER);
BTEQ> DROP TABLE Proc_Error_Tbl;
BTEQ> CALL spSample3('Richard', 100, OSqlstate, OMsg);

Example: How a Calling Stored Procedure Handles the Condition Returned By a Called Stored Procedure

The following illustrates how a calling stored procedure handles the condition returned by a called stored procedure terminated with an exception condition.

During the execution of outer stored procedure resignalsp1Example:, the CALL statement invokes the inner stored procedure resignalsp2.

In resignalsp2, the SIGNAL statement raises completion condition SQLSTATE '02000'.

The SIGNAL statement first clears the diagnostics area. Then the Statement Area is updated and a Condition Area 1 is added with a RETURNED_SQLSTATE '02000'. The CONTINUE HANDLER is invoked for this condition.

The RESIGNAL statement raises user-defined condition nodata.

The Statement Area is updated with the details of the RESIGNAL statement.

Condition Area 1 is pushed down in the Diagnostics Area as Condition Area 2, and a new Condition Area 1 is added corresponding to nodata.

There is no handler for this user-defined condition and resignalsp2 is terminated with exception condition ERRRTSNOCOND (SQLCODE 7603 and SQLSTATE '45000').

In resignalsp1, the CALL statement reports exception ERRRTSNOCOND that is handled by the condition handler declared to handle SQLSTATE ‘45000’ in cs2.

The GET DIAGNOSTICS statement retrieves Condition Area 1 from the Diagnostics Area and assigns 'nodata' to condname.

The RESIGNAL statement raises user-defined condition nodata that is handled by the handler defined for nodata in the inner procedure.

The CALL statement returns OParam1 = 0, pcondno = 1, and count = 0.

The outer stored procedure is defined as follows:

CREATE PROCEDURE resignalsp1 (OUT   OParam1 INTEGER,
                              INOUT pcondno INTEGER,
                              OUT   count   INTEGER)
cs1 :BEGIN
     DECLARE nodata CONDITION;
     DECLARE cnt INTEGER DEFAULT VALUE 0;
     DECLARE CONTINUE HANDLER FOR nodata
         SET count = 0;
     cs2:BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '45000'
         BEGIN
           GET DIAGNOSTICS EXCEPTION pcondno
               condname = CONDITION_IDENTIFIER;
           IF (condname = 'nodata') THEN
               RESIGNAL 'nodata';
           END IF;
         END;
          SET OParam1 = 0;
          CALL resignalsp2(cnt); /* returns exception '45000' */
     END cs2;
END cs1;

The inner stored procedure is defined as follows:

CREATE PROCEDURE resignalsp2 (OUT OParam1 INTEGER)
cs1 :BEGIN
     DECLARE cnt INTEGER DEFAULT VALUE 0;
     cs2:BEGIN
         DECLARE nodata CONDITION;
         DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                RESIGNAL nodata;
         SET OParam1 = 0;
         SELECT COUNT(*) INTO cnt FROM tab1;
          IF (cnt = 0) THEN
            SIGNAL SQLSTATE '02000';
            …
          ELSE
             SET OParam1 = cnt;
          END IF;
     END cs2;
END cs1;
BTEQ> CREATE SET TABLE  tab1 (c1 INTEGER);
BTEQ> CALL resignalsp1(OParam1, 1, condname);

Example: Retrieving Information in a RESIGNAL Statement

The following example illustrates the retrieval of information corresponding to Condition Area 2 when a user-defined condition is specified in a RESIGNAL statement.

During the stored procedure execution, the SELECT … INTO statement raises completion condition SQLSTATE '02000'.

The Statement Area is filled with the details of the SELECT … INTO statement and a Condition Area is added to the Diagnostics Area with information related to the completion condition.

The handler for SQLSTATE ‘02000’ is invoked to handle completion condition SQLSTATE '02000'.

The RESIGNAL statement within the handler action raises user-defined condition nodata.

Condition Area 1 is pushed down in the Diagnostics Area to Condition Area 2, and a new Condition Area 1 is added to the Diagnostics Area corresponding to the user-defined condition nodata.

User-defined condition nodata is propagated to the outer compound statement.

The nodata handler in compound statement cs1 handles user-defined condition nodata.

The first GET DIAGNOSTICS statement retrieves Condition Area 1 and assigns 'nodata' from CONDITION_IDENTIFIER to condid.

The second GET DIAGNOSTICS statement retrieves Condition Area 2 and assigns SQLSTATE '02000' from RETURNED_SQLSTATE to sqlstate1.

Stored procedure execution resumes after the operations preceding END cs2. The CALL statement returns OParam1 = 0, pcondno = 2, and sqlstate1 = '02000'.

CREATE PROCEDURE resig6 (OUT   OParam1 INTEGER,
                         INOUT pcondno INTEGER,
                         OUT   sqlstate1 CHAR(5),
                         OUT   condid CHAR(10))
cs1 :BEGIN
     DECLARE nodata CONDITION;
     DECLARE CONTINUE HANDLER FOR nodata
     BEGIN
         GET DIAGNOSTICS EXCEPTION 1
             condid = CONDITION_IDENTIFIER;
         GET DIAGNOSTICS EXCEPTION pcondno
             sqlstate1 = RETURNED_SQLSTATE;
     END;
      cs2: BEGIN
           DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
           BEGIN
               RESIGNAL nodata;
               …
           END;
          SET OParam1 = 0;
          SELECT c1
          INTO OParam1 FROM tab1; -- Returns warning  NO DATA FOUND
          …
      END cs2;
END cs1;
BTEQ> CREATE SET TABLE  tab1 (c1 INTEGER);
BTEQ> CALL resig6(OParam1,2,sqlstate1, condid);

Example: Pushing Out a Condition When the Diagnostics Area is Full

The following example illustrates that when the Diagnostics Area is full, Condition Area 16 is pushed out to accommodate another condition. An SQLSTATE is specified in a RESIGNAL statement.

During the stored procedure execution, the SELECT … INTO statement in cs16 raises completion condition SQLSTATE '02000'.

The Statement Area is filled in with the details of the SELECT … INTO statement and a Condition Area 1 is added in the Diagnostics Area.

The handler in cs16 is invoked for completion condition SQLSTATE '02000'. The RESIGNAL statement in cs16 raises condition '23505'.

The existing Condition Area is pushed down in the Diagnostics Area its condition number is incremented by 1.

The Statement Area is updated and a new condition area with Condition Number 1 is added in the Diagnostics Area.

The condition is handled by the handler defined in cs15. The RESIGNAL statement in cs15 raises condition SQLSTATE '23505'.

The existing Condition Areas are pushed down in the Diagnostics Area. Condition Area 2 becomes Condition Area 3, and a new Condition Area 1 is added for condition SQLSTATE '23505'. This shifting of Condition Areas and adding of a new Condition Area happens for all the RESIGNAL statements.

Finally, the RESIGNAL statement in cs2 raises condition SQLSTATE '23505', a new Condition Area 1 is added at the top of the Diagnostics Area, and all other Condition Areas are pushed down by one position.

The condition is handled by the handler in cs1 and the RESIGNAL statement in cs1 raises condition SQLSTATE '23505'.

Now the total number of conditions in the Diagnostics Area has reached 16, the limit on the maximum number of Condition Areas that can be stored there.

Condition Area 16 is moved out of the Diagnostics Area, all other Condition Areas are pushed down by 1 position, and the new condition is added as Condition Area 1.

NUMBER in the Statement Area remains at 16 and MORE is set to Y. The condition raised is handled by the CONTINUE handler in cs0.

The GET DIAGNOSTICS statement in cs0 retrieves RETURNED_SQLSTATE '23505' from Condition Area 16 and assigns it to sqlstate1. Because the handler type is CONTINUE, the stored procedure continues at the SET statement in cs16.

The CALL statement returns OParam1 = 0 and sqlstate1 = '23505'.

CREATE PROCEDURE resig6 (OUT OParam1 INTEGER,
                         OUT sqlstate1 CHAR(5))
cs0: BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
 GET DIAGNOSTICS EXCEPTION 16
     sqlstate1 = RETURNED_SQLSTATE;
 cs1: BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    RESIGNAL SQLSTATE '23505';
  cs2: BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
     RESIGNAL SQLSTATE '23505';
   cs3: BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
      RESIGNAL SQLSTATE '23505';
    cs4: BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
       RESIGNAL SQLSTATE '23505';
     cs5: BEGIN
     DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
        RESIGNAL SQLSTATE '23505';
      cs6: BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
         RESIGNAL SQLSTATE '23505';
       cs7: BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
          RESIGNAL SQLSTATE '23505';
        cs8: BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
           RESIGNAL SQLSTATE '23505';
         cs9: BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
            RESIGNAL SQLSTATE '23505';
          cs10: BEGIN
          DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
             RESIGNAL SQLSTATE '23505';
           cs11: BEGIN
           DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
              RESIGNAL SQLSTATE '23505';
            cs12: BEGIN
            DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
               RESIGNAL SQLSTATE '23505';
             cs13: BEGIN
             DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
                RESIGNAL SQLSTATE '23505';
              cs14: BEGIN
              DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
                 RESIGNAL SQLSTATE '23505';
               cs15: BEGIN
               DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
                  RESIGNAL SQLSTATE '23505';
                cs16:BEGIN
                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                   RESIGNAL SQLSTATE '23505';
                SELECT c1 INTO OParam1 from tab1;
                -- Returns warning  NO DATA FOUND
                SET OParam1 = 0;
                END cs16;
               END cs15;
              END cs14;
             END cs13;
            END cs12;
           END cs11;
          END cs10;
         END cs9;
        END cs8;
       END cs7;
      END cs6;
     END cs5;
    END cs4;
   END cs3;
  END cs2;
 END cs1;
END cs0;
BTEQ> CREATE SET TABLE  tab1 (c1 INTEGER);
BTEQ> CALL resig6(OParam1, sqlstate1);

Related Topics

For more information about: