February 10, 2014 at 4:30 am
Use [MyDatabase]
IF object_id('#Temp_Table') IS NOT NULL
BEGIN
DROP TABLE #Temp_Table
Print 'before'
END
CREATE TABLE #Temp_Table (
[TempTableID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int])
IF object_id('#Temp_Table') IS NOT NULL
BEGIN
DROP TABLE #Temp_Table
Print 'after'
END
If I write the above into Query Analyzer and run it - first time it runs fine.
Second and subsequent times, it says 'There is already an object named '#Temp_Table' in the database. Why does it ignore the test to see if the table exists?
If I change the test to:
IF object_id('tempdb..#Temp_table') IS NOT NULL
it works fine. Why is that?
This has got me thinking - and worrying. I have probably half a dozen stored procedures within which I create a #Temp_Table with the test I have shown above (at the top). I have never had one fall over and report the 'Temp_Table' already exists etc.
Does the code (shown above, at the top) work within stored procedures but not in Query Analyzer?
What happens if in one stored procedure you create #Temp_Table and, for some reason, it does not get dropped. Then, in another stored procedure, an attempt is made to create it again?
If one has to use a Temporary Table within a stored procedure, is it good practice to make sure they have unique names?
February 10, 2014 at 4:39 am
It's not ignoring your test, it's checking to see if there's a table named '#Temp_Table' in the current database. Unless your current database context is TempDB, that test will fail as the temp table is in TempDB, not in your current user database
Does the code (shown above, at the top) work within stored procedures but not in Query Analyzer?
No. Same behaviour everywhere
What happens if in one stored procedure you create #Temp_Table and, for some reason, it does not get dropped. Then, in another stored procedure, an attempt is made to create it again?
If you create a temp table in a procedure, it's automatically dropped as soon as the procedure ends.
If one has to use a Temporary Table within a stored procedure, is it good practice to make sure they have unique names?
No, but I do prefer to name temp tables based on what goes in them or what they're used for. Nothing annoys me more than a procedure that has #Temp1, #Temp2, #Temp3, etc as temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2014 at 6:25 am
Thank you for your reply.
February 10, 2014 at 8:14 am
I prefer to include the object type parameter in the OBJECT_ID function call to make sure it is actually a temp table, and not some other type of temp object.
In the example below, the second parameter, 'U', specifies a user table.
if object_id('tempdb..#MyTemp','U') is not null begin drop table ..#MyTemp end
February 10, 2014 at 8:25 am
GilaMonster (2/10/2014)
What happens if in one stored procedure you create #Temp_Table and, for some reason, it does not get dropped. Then, in another stored procedure, an attempt is made to create it again?
If you create a temp table in a procedure, it's automatically dropped as soon as the procedure ends.
Just to be clear, Gail is referring to local temp tables (#). Global temp tables (##) might not be dropped at the end of the procedure as stated on BOL.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply