Archives of the TeradataForum

Message Posted: Thu, 21 Feb 2008 @ 10:29:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

        ...
     BEGIN
          -- Code not shown
          . . .
          drop_table_block:
          BEGIN
                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 '
                       ||TRIM(v_result_db)
                       ||'.'
                       ||TRIM(v_result_table);

                 /* Execute the sql string to drop the table */
                 CALL DBC.SYSEXECSQL(v_drp_tbl_str);
                 -- if you need to know whether it was deleted
                 -- check table_not_there variable here.

          END drop_table_block;

     -- more stuff here
     . . .

     END;

The SQLSTATE '42000' is SQLCODE of '3807'

	<Prev	Next>		<<First	<Prev	Next>	Last>>