Does anyone know how to test if a temporary table exists?

Just: select 1 from temptab

--
Hartmut Branz
International and Sustaining Engineering
iAnywhere Solutions


Maggie Lastovica wrote:

Try a variation of this:

alter function check_for_foo ()
returns int
begin
declare table_not_found exception for sqlstate '42W33';
declare tmp int;
begin
select first 1 into tmp from foo;
exception
when table_not_found then
select 0 into tmp;
end;
return tmp
end;
go
select check_for_foo()

--
Mark Culp
ASA Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
* <<<<< Please always include the ASA version and build number >>>>> *
* <<<<< _and_ the operating system version that you are using >>>>> *
-------------------------------------------------------------------------

Maggie Lastovica wrote:

I have only tested this function in 9.0.0.1235. It returns 1 if the
table name is OK, 0 otherwise. It is a horrible kludge; there should
be something easier ...

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;


Breck

On 5 Dec 2003 07:41:37 -0800, "Maggie Lastovica"
<m.lastovica (AT) ssss (DOT) com> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

Just call function object_id( 'name_of_the_table' ). It will return null
if there are not table with name 'name_of_the_table'.

Best regards, Serge Gavrilov.

Maggie Lastovica wrote:

object_id does not work for temporary tables.

Breck

On 8 Dec 2003 04:11:56 -0800, Serge Gavrilov <sergegavrilov (AT) mail (DOT) ru>
wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

Breck Carter [TeamSybase] wrote:
It works for the GLOBAL TEMPORARY TABLEs

Best regards, Serge Gavrilov