EXEC for multiple lines

[Former Member]

Hi all,

I am trying to execute a SQL statement with multiple lines with EXEC in a stored procedure, but I cannot get it to work.

As an example, my procedure should look as follows:

v_string VARCHAR (5000);

/********* Begin Procedure Script ************/

BEGIN

v_string :=

'DROP TABLE ICV_HCV.TEST;

CREATE TABLE ICV_HCV.TEST(A INT)';

EXEC :v_string;

END;

/********* End Procedure Script ************/

The activation of the procedure works fine. However, I always get an error when calling the procedure.

So I tried different ways to create v_string.

1) I added a line break after the semicolon of the first statement:

v_string :=

'DROP TABLE ICV_HCV.TEST; ' || char(13) || char(10) ||

'CREATE TABLE ICV_HCV.TEST(A INT)';

When I do SELECT :v_string from dummy it shows the statement correctly with a line break, but the procedure call does not work anyway.

2) I left the semicolon away. Then I got the error 'incorrect syntac near "CREATE".

Is there any way to execute multline SQL statements in one EXEC call?

Thanks in advance!

[Former Member · Accepted solution]

I solved the problem.

The simple example with DROP TABLE and CREATE TABLE in one string did not work since they are really two separate stataments.

However, I could use the two different statements withing the whole trigger statement, because it is recognized as one statement. The code for that is as follows:

v_create_trigger :=
'CREATE TRIGGER ' || :trigger_name || ' AFTER INSERT ON ' || :schema_name || '.' || :fact_table_name || char(13) || char(10) ||
' REFERENCING NEW ROW new_row ' || char(13) || char(10) ||
' FOR EACH ROW ' || char(13) || char(10) ||
' BEGIN ' || char(13) || char(10) ||
   ' DELETE FROM ' || :schema_name || '.' || :delta_table_name  || --delete old records
' WHERE (GUID, ITEM_GUID, SALES_ORG, PARTNER_NUMBER, CURRENCY, ALLOC_DATE) ' ||--key
'= ( :new_row.GUID, :new_row.ITEM_GUID, :new_row.SALES_ORG, :new_row.PARTNER_NUMBER, :new_row.CURRENCY, :new_row.ALLOC_DATE);' || char(13) || char(10) ||
' INSERT INTO "ICV_HCV"."DELTAS_IS" (GUID, ITEM_GUID, SALES_ORG, PARTNER_NUMBER, CURRENCY, ALLOC_DATE, REVENUE)
VALUES (:new_row.GUID, :new_row.ITEM_GUID, :new_row.SALES_ORG, :new_row.PARTNER_NUMBER,
:new_row.CURRENCY, :new_row.ALLOC_DATE, :new_row.REVENUE);' || char(13) || char(10) ||
' END';

SELECT :v_create_trigger from dummy;

Hope this perhaps helps someone else
Regards

Sabrina

[former_member182302 · Accepted solution]

I meant a different statement here. You have the following statements:

1) Delete

2) Insert

If you have deleted the old records and then the insertion failed due to some issues like "duplicate records" or any other error message. Then your table will have no data right?

I was referring on how to handle the above scenario

Nice work around Thanks for sharing Sabrina

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

thanks for your help. I checked the documents, but I am not really sure how to apply any of these to my case?

The problem is that I need to put my create statement in a string and use EXEC.

Otherwise I cannot parameterize the create statement.

I could not derive a solution for that from your proposals, did I miss something?

Regards

Sabrina

[former_member182302]

Hi Sabrina,

Why do you want to execute all the SQL statements you are framing at once. Separate them and execute

DROP PROCEDURE TEST;
CREATE PROCEDURE TEST ()
AS

BEGIN
DECLARE v_string1 VARCHAR(1000);
DECLARE v_string2 VARCHAR(1000);

v_string1 :='DROP TABLE SYSTEM.TESTTABLE';
v_string2 :='CREATE TABLE SYSTEM.TESTTABLE(A INT)';

EXEC :v_string1;
EXEC :v_string2;
END;

call TEST;

Is there any specific reason why do want to execute the multiple statements at once?

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

thanks for you reply.

Yes, I neet to execute them at once. The code above was only an example.

Actually I want to use the procedure to automatically create a trigger on a table. So my v_string would look like:

v_string :=

'CREATE TRIGGER <trigger_name>

AFTER INSERT ON' || :schema_name || '.' || :fact_table_name ||

'REFERENCING NEW ROW new_row ' ||

'FOR EACH ROW '||

'BEGIN ' ||

      'DELETE FROM' || :schema_name || '.' || :delta_table_name || --delete old records

      'WHERE ... ;'

      'INSERT INTO <table_name> VALUES (...);' || --insert new records

'END'

So you see that my trigger statement contains a delete and a insert statement.

But the whole 'create trigger' statement has to be executed as once.

[former_member182302]

I see what you are saying. You want to have a control such that both DELETE and INSERT statements get executed at once. Rather you want to have a commit on the database only after both the statements are executed successfully right?

Have a look on the below documents:

Regards,

Krishna Tangudu