Droping Tables

UDA

Droping Tables

Have a procedure that's supposed to drop a table that it's pulling for a query. Here's what I have.

BEGIN

DECLARE newcount INTEGER;
DECLARE tablename1, tablename2 VARCHAR(200);

DECLARE TableCur CURSOR FOR

SELECT
tablename1,tablename2
FROM database.view
FOR READ ONLY;
OPEN TableCur;

SELECT COUNT(*) FROM database.view
INTO newcount;
REPEAT
FETCH TableCur INTO tablename1,tablename2;

DROP TABLE :tablename1 ;
DROP TABLE :tablename2 ;

SET newcount = newcount - 1;
UNTIL newcount = 0
END REPEAT;
CLOSE TableCur;
END;

It's not liking the highlighted area. What's the issue with a drop statement that gets it's table name from a query?
1 REPLY
Enthusiast

Re: Droping Tables

Hi,

basically procedure does't support DDL for this you have to try dynamic SQL,
Try this and let me know it will work