RE: Error Checking in SQL Stored Procedure is not Working

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 02/09/2018
03:29:25 AM:

Your dynamic SQL Statement is not prepared because the Table does not

exist?

... you have to trap SQLSTATE 70003

Yes, the PREPARE is unsuccesful because the table does not eist 00
but the state that comes back in the GET DIAGNOSTICS is '00000'. I never
see stat '70003'. Why is that?

If you want to check whether a table exists in a specific library /

schema,

why not accessing the SYSTABLES catalog view?
You don't even need a dynamic SQL Statement.
Something like this

Select 1 into isFound
Where Table_Name = 'YOURTABLE' and Table_Schema = 'YOURLIB';

Sounds like a good idea -- anything to eliminate the dynamic SQL.
I will try that. Thanks.

BTW are you sure you don't want to set the Error message within the

Compound

Statement of the Handler?

I've found that setting the error text right before the statement
that might fail works well to pass that error text into the global
Exception Handler that I have.


Sincerely,

Dave Clark