Error in executing single dynamic query that has multiple SQL statements

[sujathaeeshan]

Hello,

The single dynamic query has multiple SQL statements.

for ex:

@query="DELETE FROM TABLE L.TAB1 WHERE COL1='VAL1' ;
DELETE FROM TABLE L.PTR1 HERE COL1='VAL1' ;
DELETE FROM TABLE L.ITR1 WHERE COL1='VAL1' ;"

The stored procedure that executes this statement in SQL server looks like

ALTER PROCEDURE [dbo].[TABEXECUTE]
(
@query ntext
)
AS
BEGIN
EXEC sp_executesql @query

END

It runs successfully on SQL Server. However, it fails for DB2 when i try to execute this query using EXECUTE IMMEDIATE.

As i understand, DB2 keyword EXECUTE IMMEDIATE executes single query that has no multiple statements. Hence, it is not working for the mentioned scenario.

Please let me know if there is any alternative approach or db2 keyword to run single dynamic query that has multiple statements in DB2.
(Note: I can only pass single dynamic query as parameter to stored procedure. I would really appreciate if your logic or approach is closed to this criteria)

Thanks in advance for your help!!

[Rabbit]

I don't understand the purpose of this stored procedure... Why create a stored procedure whose only purpose is to call another stored procedure? Just skip that middle step and call it directly.

[sujathaeeshan]

Thanks for the reply

Here, I am just passing a dynamic query as parameter from application to stored procedure. There is no stored procedure calling another stored procedure

[Rabbit]

Yes there is.

You are calling the stored procedure named TABEXECUTE. And all that does is call the stored procedure sp_executesql.