2

I want to write a procedure in SQL anywhere which can check if a local temporary table exists and if it does use it. I do not want to drop the table. I have already found a way to drop local temporary table which is:

DROP TABLE IF EXISTS t;

I have also tried following: I created a local temporary table TEMP_TABLE. Then I tried to run this query:

select object_id('tempdb..TEMP_TABLE')

This just gives me NULL. But if I try 

select * from TEMP_TABLE

it works perfectly fine.

So can anyone please help me find a way to check if the local temporary table exists in sql anywhere.

krisku
  • 3,916
  • 1
  • 18
  • 10
Ankur Patel
  • 1,413
  • 8
  • 14

2 Answers2

3

I'm not sure what version of Sybase you have but this works in Sybase 11 so I can imagine it will work in any version up too:

Begin
Create local Temporary table TEMP_TABLE (column1 int); //Create temp table
// any other code needed to be executed if table did not exist
Exception when others then
// Code to be executed when table does exist
end;

This is basically a try..catch for sybase. If the Temp Table exists it will throw an exception, in the exception you can run the code you want to knowing that the table already exists.

Christian Barron
  • 2,695
  • 1
  • 14
  • 22
  • Just out of curiosity, do you know why the tempdb syntax does not work in sql anywhere? And I am using sql anywhere 16. – Ankur Patel May 26 '15 at 13:18
  • I don't actually know why. It's a bit strange to be honest. It works if it's executed within the same transaction but if you're looking for a temp table that already exists it just doesn't seem to like it but I'm not sure why. – Christian Barron May 26 '15 at 13:21
-3

In one query you are referring database and in other you are not, try below two queries. 

 select object_id('tempdb..TEMP_TABLE')
 select * from tempdb..TEMP_TABLE


select object_id('TEMP_TABLE')
select * from TEMP_TABLE
Almas Mahfooz
  • 896
  • 1
  • 10
  • 19
  • in question it is not saying SAP SQL, it is just saying SQL. My queries will work with work with Microsoft SQL Server. – Almas Mahfooz May 22 '15 at 06:29
  • The OP did state "SQL Anywhere", except with a lowercase 'a'. – RobV May 22 '15 at 06:56
  • Find if the local temporary table exists in **sql anywhere and use it and SQL Anywhere**, the sql anywhere in question sounds more like if person is asking for how to access temp table anywhere in SQL. – Almas Mahfooz May 22 '15 at 07:00
  • more confusing was that we have object_id in SQL and tempdb. Also we refer database with double dots like tempdb..Temp_TABLE. and both queries mentioned in question does work in SQL from anywhere throughout tempdb and other databases. – Almas Mahfooz May 22 '15 at 07:09
  • The question says "SQL anywhere" and is tagged "sybase" and "sqlanywhere". I think it's pretty evident that he's not looking for something that works with MS SQL Server. – Graeme Perrow May 24 '15 at 03:36
  • Question itself is not pretty evident but a little obscure and confusing, and I first read the question and just after comments saw the tags. And in question sql anywhere is not written like "SQL Anywhere" so anyone who is in the world never heard anything about "SQL Anywhere" could think it's in sql anywhere (any place , in any table, in any database etc). it's stupid to down vote someone on their minor mistake, misunderstanding. it seems like people here are waiting to punish other with their comments for being ignorant of SQL Anywhere. – Almas Mahfooz May 25 '15 at 06:12