Execute Multiple Drop Table Statements in single Procedure

Database

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

2 REPLIES
Junior Contributor

Re: Execute Multiple Drop Table Statements in single Procedure

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

Teradata Employee

Re: Execute Multiple Drop Table Statements in single Procedure

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