August 29th, 2012, 04:44 AM
Error in executing single dynamic query that has multiple SQL statements?
The single dynamic query has multiple SQL statements.
@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]
EXEC sp_executesql @query
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!!