October 19, 2006 at 11:12 am
Hi All,
Below is my command
if exists (select name from tempdb..sysobjects where name='#temp1')
Begin
Drop table #temp1
end
When i execute the command it gives the error the table is not existing
But when i query the tempdb
select * from tempdb..sysobjects
i find the table name as '#temp1____________________123433'
How can i overcome this problem
Regards
Suresh
October 19, 2006 at 12:01 pm
Hello Suresh,
You can make your IF statement look like
IF Exists (Select name from tempdb..sysobjects where name like '#temp1%')
Check the select statement before incorporating it in the IF statement, so that you can come to a conclusion whether it is displaying the correct results or not.
Hope this helps.
Thanks
Lucky
October 19, 2006 at 12:45 pm
That's the safest way to do it :
CREATE TABLE #A (n int)
SELECT * from #A
IF 0 < OBJECT_ID('tempdb..#a')
DROP TABLE #A
SELECT * from #A
--generates error because table is dropped
The like solution may create an error because another user may have already created the temp table for him and you'll find a hit. But when you'll try the drop, you'll get an error because it's actually not your table.
October 20, 2006 at 8:02 am
Take a look at the context of your connection. I noticed that you say select ... from tempdb..sysobjects, but your drop table statement does not include tempdb.."table_name". Try adding "tempdb.." to your drop statement.
October 20, 2006 at 8:08 am
Won't work because the object is only contained in tempdb. You can actually access the table from any other database as long as you are still using the same connection (and that connection hasn't been closed are reset by connection pooling).
Check this out :
USE master
CREATE TABLE #A (n int)
SELECT * from #A
USE msdb
SELECT * from #A
IF 0 < OBJECT_ID('tempdb..#a')
DROP TABLE #A
SELECT * from #A
--errors out because the object is dropped
October 20, 2006 at 8:11 am
Just to complete and correct what I said :
IF 0 < OBJECT_ID('tempdb..#a')
DROP TABLE tempdb..#a
whill throw this message :
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
but it won't error out.
October 20, 2006 at 9:32 am
The prefix is necessary for the OBJECT_ID function, but not in DROP TABLE. This version doesn't generate any errors or warnings:
IF 0 < OBJECT_ID('tempdb..#a')
DROP TABLE #a
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply