SQL Server 'ignores' drop table command

Question

I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the following

create table #TempTable1 (Name varchar(30))

-- Some queries involving #TempTable1

drop table #TempTable1

-- Some queries

declare MyCursor cursor fast_forward for 
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

       create table #TempTable1 (Name varchar(30))

       -- Some queries involving #TempTable1

       drop table #TempTable1 

       fetch next from MyCursor into @CursorValue

    end

close MyCursor 
deallocate MyCursor

My query fails with an error in the second create table

There is already an object named '#TempTable1' in the database.

I know that due to query optimizations the commands are not running in the sequence I am typing them. However this error still seems strange to me. I can put some go commands to force the execution, but this will create some other problems in my code e.g. I will have to redeclare some variables e.t.c. Renaming the tables will solve the problem but it will harm a little the readabillity of the code. So it is more 'curiocity' than an 'actual problem'.

Any suggestions on why this is happening and how I can avoid it?

Do you require the first temptable `#TempTable1` inside the cursor — Shanky, Feb 22 '16 at 07:47
As I said, I can solve the problem by renaming the table, but out of curiocity I want to know why this is happening. Moreover the `#TempTable1` is not the real name of my table, I am using something more descriptive based on its contents. — Athafoud, Feb 22 '16 at 08:07
I have updated my answer please see. You are not allowed to create temptable with same in single query or batch — Shanky, Feb 22 '16 at 09:21
Be aware of [temp table caching](http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx). — Michael Green, Feb 23 '16 at 09:26

Shanky · Accepted Answer · 2016-02-22T09:50:56.613

You can see you are creating temptable with same name.You cannot create temp table with same name in single query or batch this is documented as per BOL document. From Bol document

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

You should use below logic it will just check before creating and deleting

IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

   DROP TABLE #Temptable1

CREATE TABLE #Temptable1(name nvarchar(30))

Which would make your query like

IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

       DROP TABLE #Temptable1

    CREATE TABLE #Temptable1(name nvarchar(30))

-- Some queries involving #TempTable1

   IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

       DROP TABLE #Temptable1



-- Some queries

declare MyCursor cursor fast_forward for 
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

          IF OBJECT_ID('tempdb..#Temptable2') IS NOT NULL--new temp table

       DROP TABLE #Temptable2

    CREATE TABLE #Temptable1(name nvarchar(30))

       -- Some queries involving #TempTable1

        IF OBJECT_ID('tempdb..#Temptable2') IS NOT NULL

       DROP TABLE #Temptable2

       fetch next from MyCursor into @CursorValue

    end

close MyCursor 
deallocate MyCursor

I was searching for the very same resource! I was not aware that SQL Server behaves that way on create table. — Athafoud, Feb 22 '16 at 10:16
Just a side note to your anwser, checking if a table exists or not, is not a solution to my problem. The only solution is to rename one of the Tempopary Tables, or to truncate it instead of droping it. — Athafoud, Feb 22 '16 at 10:29
Yes you are correct that is why I mentioned it at top in my answer but if you are creating and dropping multiple temp tables in your query I have always seen checking avoids any unforeseen error. — Shanky, Feb 22 '16 at 10:32

SQL Server 'ignores' drop table command

1 Answers1