Run all SQLs in stored procedure even if you get error

Sometimes we may want Stored Procedure to execute all SQL statements even if there is error. Generally, Stored Procedure exit as soon as it encounters any error. However we may come across situation where we wish to execute all the SQL statements in Stored Procedure even if an error is encountered. We can achieve this my adding a CONTINUE handler for SQLEXCEPTION. If you wish to add the exception details , you can add it in handler. Lets look at the below example:

REPLACE PROCEDURE TST_HANDLER(OUT ENAME VARCHAR(30))
SQL SECURITY OWNER
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLEXCEPTION
BEGIN
END;
SEL FNAME INTO :ENAME FROM EMPLOYEE WHERE FNAME='JACOB';
SEL FNAME INTO :ENAME FROM EMPLOYE WHERE FNAME='JOHN';
SEL FNAME INTO :ENAME FROM EMPLOYEE WHERE FNAME='GREEN';
END;

To run the procedure :

CALL TST_HANDLER(ENAME) ;

In the above example, in the second SEL statement , the tablename is wrong hence it will fail with Table Does Not Exist error. However, since we have added CONTINUE handler for SQLEXCEPTION hence, it will continue executing further SQL statements.

One thought on “Run all SQLs in stored procedure even if you get error

  1. Hi Raj, Sorry couldn’t find a specific place to send an email so I am making this request here. I am currently working on teradata database and find your blog very useful. However, for one thing I am most interested to learn is using teradata utilities like MLOAD, FastLoad, FastExport etc. If you have been using this utilities would you be able to make some posts on how to use those utilities. It will be really helpful.

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *