title: Error handling in Teradata Stored Procedure
body: <p>I am trying to develop a stored procedure within TERADATA to handle and manage exceptions. </p>
  
  <p>The stored procedure should raise the error to the caller, which is an SSIS Package.</p>
  
  <p>I am trying to illustrate this by creating a stored procedure for illustration only.   </p>
  
  <p>I have these tables:</p>
  
  <p><strong>Table_A</strong>:</p>
  
  <pre><code>- ID           INT
  - ITEM_NUM     INT
  - DESC         VARCHAR(20)
  - CREATE_DTTM  VARCHAR(2O)
  </code></pre>
  
  <p><strong>Table_B</strong>:</p>
  
  <pre><code>- ID           INT
  - ITEM_NUM     INT
  - DESC         VARCHAR(20)
  - CREATE_DTTM  VARCHAR(2O)
  </code></pre>
  
  <p>I have two tables that will be inserting data from two SELECT statements. </p>
  
  <pre><code>REPLACE PROCEDURE csTest2()
  SQL SECURITY OWNER
  BEGIN
      DECLARE varErrorMessage char(256);
      DECLARE varSQLState char(5);
      DECLARE varReturnCode char(5);
  
      DECLARE varRollbackNeededInd char(1);           /* transaction mgt */
      SET varRollbackNeededInd = 'N';
  
      SET varReturnCode = '00000';
      SET varErrorMessage = '';
  
      BEGIN TRANSACTION;
  
      -- USING A SINGLE HANDLER WITH MULTIPLE STATEMENTS
      -- PLANING TO CHANGE ERROR MESSAGE IN EACH STATEMENT.
      ins6: BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  
          H99:Begin
              set varSQLState = SQLSTATE;
              set varErrorMessage= 'This message should not be displayed'; -- 
          end H99;
  
          -- IMAGINE THAT I AM GETTING THE VALUES AS INPUT PARAMERS IN THE PROCEDURE 
          INSERT INTO "Table_A"
           (ID , ITEM_NUM, DESC, CREATE_DTTM)
           SELECT 1, '222', 'SOME DESC',CURRENT_TIMESTAMP;
  
  
           H98:Begin
               set varSQLState = SQLSTATE;
               set varErrorMessage= 'This message is displayed, ITEM_NUM invalid characters';
           end H98;
  
          -- NOW I AM DOING A SECOND INSERT TO table b WITH INVALID DATA
          -- THE VALUE FOR THE ITEM NUMBER CONTAINS ALPHANUMERICE CHARACTERS
          INSERT INTO "Table_b"
           (ID , ITEM_NUM, DESC, CREATE_DTTM)
           SELECT 1, '333F', 'SOME DESC',CURRENT_TIMESTAMP;
  
      END ins6;
  
      EndTrans: BEGIN
  
          IF varSQLState &lt;&gt; '0' THEN
              SET varRollbackNeededInd = 'Y';
              SET varReturnCode = '9999';
          END IF;
  
          IF  varRollbackNeededInd = 'Y' THEN
              ROLLBACK;        -- ROLLBACK AND SEND ERROR TO CALLER
              SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = 'SQlState is - ' || varSQLSTATE || ' - and error is - ' || varErrorMessage;
          ELSE
              END TRANSACTION; -- COMMIT TRANSACTION
  
          END IF;
     END EndTrans;
  
  END;
  </code></pre>
  
  <p>The problem that I am facing with the above stored procedure is that error message that I get is not the one that I am expecting. Since error is intentionally created in my second statement I am expecting to get: <strong>This message is displayed, ITEM_NUM invalid characters</strong> but I am getting <strong>This message should not be displayed</strong></p>
  
  <p>Now if I modify the PROCEDURE to have multiple handlers, one for each statement, I do get the correct error message, but now since I am intentionally generating the error in the first statement it does not terminate the procedure, it handles the error and sets the proper message but continues to process the next statement which I am not expecting to do this, so how can I terminate this procedure? </p>
  
  <pre><code>REPLACE PROCEDURE csTest2()
  SQL SECURITY OWNER
  BEGIN
  
      DECLARE varErrorMessage char(256);
      DECLARE varSQLState char(5);
      DECLARE varReturnCode char(5);
  
      DECLARE varRollbackNeededInd char(1);           /* transaction mgt */
      SET varRollbackNeededInd = 'N';
  
      SET varReturnCode = '00000';
      SET varErrorMessage = '';
  
      BEGIN TRANSACTION;
  
      ins6: BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  
          H99:Begin
              set varSQLState = SQLSTATE;
              set varErrorMessage= 'Error is displayed in this case because ITEM_NUM';
  
          -- ERROR IS PRESENT IN THIS STATEMENT AND SHOULD TERMINATE THE PROCEDURE. 
          INSERT INTO "Table_A"
           (ID , ITEM_NUM, DESC, CREATE_DTTM)
           SELECT 1, '222F', 'SOME DESC',CURRENT_TIMESTAMP;
      END ins6;
  
      ins7: BEGIN      
           H98:Begin
               set varSQLState = SQLSTATE;
               set varErrorMessage= 'no error is displayed in this case';
           end H98;
  
          -- NO ERROR IS EXPECTED, BUT IT SHOULD NOT REACH HERE SINCE WE HAD ERROR ON FIRST STATEMENT. 
          INSERT INTO "Table_b"
           (ID , ITEM_NUM, DESC, CREATE_DTTM)
           SELECT 1, '333', 'SOME DESC',CURRENT_TIMESTAMP;
  
      END ins7;
  
      EndTrans: BEGIN
  
          IF varSQLState &lt;&gt; '0' THEN
              SET varRollbackNeededInd = 'Y';
              SET varReturnCode = '9999';
          END IF;
  
          IF  varRollbackNeededInd = 'Y' THEN
              ROLLBACK;        -- ROLLBACK AND SEND ERROR TO CALLER
              SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = 'SQlState is - ' || varSQLSTATE || ' - and error is - ' || varErrorMessage;
          ELSE
              END TRANSACTION; -- COMMIT TRANSACTION
  
          END IF;
     END EndTrans;
  
  END;
  </code></pre>