How to check if a temporary table exists in SQL Anywhere?

Question

I want to write a SQL IF statement that checks if a local temporary table exists, but these tables are not written to the SQL Anywhere system directory.

Answer 1

Note that you can do this in 11.0.1 and higher:

DROP TABLE IF EXISTS t;

Answer 2

If you ask the question: "How can I delete a local temporary table without raising an error if it does not exist?" then the answer is simple: just DROP and ignore any error:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

" t?" SQLSTATE. :

• , SELECT, IF.

• EXECUTE IMMEDIATE , .

• TOP 1 , , .

• ORDER BY 1 , TOP .

• SELECT 1 .

• INTO @dummy , SELECT (, , EXECUTE IMMEDIATE) .

SELECT , SQLSTATE "00000" , "02000" . SQLSTATE ... , .

CREATE FUNCTION f_table_is_ok
   ( IN @table_name VARCHAR ( 128 ) )
   RETURNS INTEGER
   ON EXCEPTION RESUME
BEGIN
   DECLARE @dummy INTEGER;
   EXECUTE IMMEDIATE STRING (
      'SELECT TOP 1 1 INTO @dummy FROM ',
      @table_name,
      ' ORDER BY 1' );
   IF SQLSTATE IN ( '00000', '02000' ) THEN
      RETURN 1
   ELSE
      RETURN 0
   END IF;
END;

:

BEGIN
DECLARE LOCAL TEMPORARY TABLE tt ( c INTEGER );
DECLARE LOCAL TEMPORARY TABLE "t t" ( c INTEGER );
SELECT f_table_is_ok ( 'asdf' );
SELECT f_table_is_ok ( 'tt' );
SELECT f_table_is_ok ( '"t t"' );
SELECT f_table_is_ok ( '"SYS"."SYSTABLE"' );
END; 

Answer 3

just try to drop it anyway and ignore the error ...

START
   DROP TABLE table,
EXCEPT WHEN OTHER THEN

END;