Archives of the TeradataForum
Message Posted: Thu, 21 Feb 2008 @ 10:29:22 GMT
| Subj: || || Re: Conditional Table Drop |
| From: || || Stegelmann, Rolf |
To answer the question, there is no clause to check for the existence directly of the table in the DROP TABLE statement. But, why would it
matter, since it if exists it will drop it. If it does not exist then it returns the 3807 error to let you know that it did not exist. The end
result is the same--the table won't exist anymore.
The pre test for the existence is kind of extra work also in the stored procedure example. You should just try and drop it. If it does not
exist the DROP TABLE won't get past the parser even. You have to use an error hander to catch the error condition. If you want to know whether it
existed or not do something like this in the SP.
-- Code not shown
. . .
DECLARE table_not_there INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET table_not_there = 1;
SET v_drp_tbl_str = 'DROP TABLE '
/* Execute the sql string to drop the table */
-- if you need to know whether it was deleted
-- check table_not_there variable here.
-- more stuff here
. . .
The SQLSTATE '42000' is SQLCODE of '3807'