All Forums Database
rahulsony111 3 posts Joined 09/11
04 Oct 2011
Execute Multiple Drop Table Statements in single Procedure

Hi,

    I have to write a procedure where i have to drop multiple tables.

I wrote it in following way

DROP TABLE Table1;

DROP TABLE Table2;

DROP TABLE Table3;

DROP TABLE Table4;

DROP TABLE Table5;

DROP TABLE Table6;

DROP TABLE Table7;

But i am getting error saying "Data definition not valid unless solitary"

I know that multiple drop tables can't be written in this way.

 

I have to find out a way to do it in single procedure.

Can anyone please help me!!!

 

Any help is appreciated.

Thanks in advance

dnoeth 4628 posts Joined 11/04
05 Oct 2011

In which session mode is your transactionn running?

If it's ANSI you must COMMIT each DROP, in BT mode you shouldn't do a BT before calling the SP.

Maybe just show your current source code.

Dieter

 

Dieter

Adeel Chaudhry 773 posts Joined 04/08
11 Oct 2011

Hi,

 

You can issue multiple DROP TABLE commands in a stored-procedure using dynamic SQL as below:

 

CALL DBC.SysExecSQL ('DROP TABLE TABLE1;');

CALL DBC.SysExecSQL ('DROP TABLE TABLE2;');

CALL DBC.SysExecSQL ('DROP TABLE TABLE3;');

 

HTH!

 

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.