April 9, 2012 at 3:46 am
My question is why this code not working
I want to check if global temp table exist if no then recreate it
If yes then drop table and then create it.
This table not exists in temp db for the first time.
IF object_id('TEMPDB.DBO.##test') IS NULL
begin
create table ##test (
counted int
)
end
else
begin
drop table ##test
create table ##test (
counted int
)
end
But – sql complain:
“There is already an object named '##test' in the database.” (Table not realy exist)
What the problem with this logic ?
Thank a lot sharon
April 9, 2012 at 3:59 am
This indeed is a very odd and strange behavior of SQL. You cannot add the definition of the temp table (CREATE TABLE #...) again in the same batch as it is validated while parsing the batch just like the declared variables. But unlike variables they are created only at runtime. You can get around this by having a different name for the temp tables OR a workaround may be possible if you can give details about your problem.
April 9, 2012 at 4:03 am
Also, since you are using global temporary tables, you can create two procedures which would check the temp table existence and create the desired temp table with different definition. And based upon the IF ELSE logic, you can execute the related procedure. This way you would be able to access the global temp table.
April 9, 2012 at 5:43 am
Try this:
IF object_id('TEMPDB.DBO.##test') IS NOT NULL drop table ##test
create table ##test
(counted int)
April 9, 2012 at 5:48 am
I would also recommend not using a global temp table. There are some edge cases where they might be necessary but as a general rule of thumb I would limit temp tables to the current session.
April 9, 2012 at 5:54 am
jewel.sacred (4/9/2012)
Also, since you are using global temporary tables, you can create two procedures which would check the temp table existence and create the desired temp table with different definition. And based upon the IF ELSE logic, you can execute the related procedure. This way you would be able to access the global temp table.
Aghhhh... I missed the whole thing. I thought the user needed to add the temp table with different definitions. Never thought that the OP was after such a simple thing :hehe:
But anyhow, I hope my response would let the OP knows why the error was generated with that code 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply