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))
DECLARE SqlTxt1 VARCHAR(5000);
DECLARE SqlTxt2 VARCHAR(5000);
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET SqlTxt1 = '
DROP TABLE '||DBName||'.PRIME_NBR;
SET SqlTxt2 = '
CREATE TABLE '||DBName||'.PRIME_NBR
PRIMARY INDEX (ROW_NBR);
Currently your handler sets the SqlTxt1 and so your proc starts with
'42000' BEGIN END;
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.
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;';
set sqltext = 'blabla;';
And individual statements are much easier to troubleshoot.
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;';