Stored Procedure - Continue Handler

Database

Stored Procedure - Continue Handler

I am running the following code, and for some reason if the table is already created this SP will fail saying the table is already created. Even thought the first SQLTEXT is to drop the actual table.

Any ideas why?

REPLACE PROCEDURE stgu16.SEC_CREATES5(IN DBName VARCHAR(30))
BEGIN
 
    DECLARE SqlTxt1 VARCHAR(5000);
    DECLARE SqlTxt2 VARCHAR(5000);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'

     SET SqlTxt1 = '
     DROP TABLE '||DBName||'.PRIME_NBR;
     ';
            
     CALL DBC.sysexecsql(:SqlTxt1);

     SET SqlTxt2 = '
       CREATE TABLE '||DBName||'.PRIME_NBR
     (
                ROW_NBR INTEGER
                                ,PRIME_NBR INTEGER
                )                
                PRIMARY INDEX (ROW_NBR);
        ';

     CALL DBC.sysexecsql(:SqlTxt2);

  END;

5 REPLIES
N/A

Re: Stored Procedure - Continue Handler

Currently your handler sets the SqlTxt1 and so your proc starts with CALL DBC.sysexecsql(NULL);

What are you trying to achieve? Ignoring the "table doesn't exist" error for the DROP?

Then you need to add BEGIN END:

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END;

 

Dieter

Re: Stored Procedure - Continue Handler

Worked great, thanks Dieter.

Re: Stored Procedure - Continue Handler

Do we have to declare two SqlTxt1 and SqlTxt2 separately or is it possible to combine both in one variable SqlTxt1 and call both? I tried to do it, but was unable to...

Let us say, if we have 20 statements, do we have to set 20 strings like this?

Please let me know if there is a way to achieve this in an easy way.

Thank You.

N/A

Re: Stored Procedure - Continue Handler

You mean multiple statements within a single SQL string?

No, dynamic multistatement request are not supported. And even if it was allowed, the example DROP/CREATE would never work, as each DDL statement must be commited.

But you don't have to use 20 different variables:

set sqltext = 'bla;';

call dbc.sysexecsql(:sqltext);

set sqltext = 'blabla;';

call dbc.sysexecsql(:sqltext);

...

And individual statements are much easier to troubleshoot.

Dieter

Re: Stored Procedure - Continue Handler

Dieter,

I mean multiple statements in a single SQL string? Looks like it is not possible....

For example, in the code below, is it possible to have multiple grants as the value of one ‘sqlstmt’  and then execute them all at once or do we have to do one at a time like grant, execute then grant and execute again.

 SET sqlstmt = 'GRANT LOGON ON ALL TO "' || TRIM(username) || '"' || 'WITH NULL PASSWORD;';

CALL DBC.SysExecSQL(:sqlstmt);

Thank You