[siddharth]

Hi,

How can we write a SQL statement to drop a table. The drop statement should not generate any error if the table doesn't exists. I have achieved the same in Oracle by catching the 'table not found exception'

The code I wrote is as follows:

CREATE PROCEDURE drop_table(pTableName VARCHAR2)
IS
vCommand VARCHAR2(1024);
table_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_not_exist,-942);
BEGIN
vCommand := 'DROP TABLE ' || pTableName || ' CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE vCommand;
EXCEPTION
WHEN table_not_exist THEN
NULL;
WHEN OTHERS THEN
RAISE;
END drop_table;

Can I do something equivalent in DB2?
Thanks,
Siddharth

[AStefan]

IS it possible to drop and create a table in sql server style?
I mean to do somethoing like that

if exists(select * from sysobjects where name = table_name and ...)
drop table table_name
else
create table table_name

In Db2 is it possible to do something similar?

[brat4]

Hi,
I have successfully used the following to continue handling in the procedure when no rows are found. I suspect if u can find out th SQLSTATE for dropping a nonexistant table, you can use the below. Do let me know if it works. To be honest I have not tried it yet.

P.N: 02000 is SQLSTATE for row not found when a sql query returns no rows


P1: Begin
DECLARE MASTER_NOT_FOUND CONDITION for SQLSTATE '02000';

DECLARE CONTINUE HANDLER for MASTER_NOT_FOUND SET MASTER_EXCEPTION_TMP='T';

<the sql to drop the table>

IF MASTER_EXCEPTION_TMP='T' THEN
<do some processing>
ELSE
<do some processing>

ENd IF
END P1

The variable MASTER_EXCEPTION_TMP gets a value of 'T' if the <sql query> returns no rows.

HTH
Cheers
Brat

[Marcus_A]

As mentioned above, you need to use "continue handling" in a stored procedure if you want to intercept and act on specific error return codes in your procedure. Otherwise DB2 will exit the routine when it thinks a fatal error has occured.