SP won't Create, Same Temp Table Twice... 1

[lameid]

I am still cutting my T-SQL teeth... I have the following error

Msg 2714, Level 16, State 1, Procedure <SP Name>, Line <Number>
There is already an object named '#Temp1' in the database.

In essence I have...

Create Procedure (@USE_Projection Bit = 0)

AS

/*
T-SQL Here
*/
IF @USE_Projection = 0
    Select <Stuff>
    Into #Temp1
    From <Tables>
Else
    Select <Similar Stuff>
    Into #Temp1
    From <Similar Tables>
End 

/*
More T-SQL Here
*/

If I drop the temp table before the second select that creates it, it will compile but the temp table would not exist at execution time....

Is there a simple fix short of creating the temp table before the if statment and using an insert into instead?

 

[gmmastros]

Is there a simple fix short of creating the temp table before the if statment and using an insert into instead?

Nope. No quick fix. There are ways around it, but I don't recommend them. I never use Select Into in any of my code. I always create the temp table first and then insert into.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

George,

Thanks for the quick reply... I guessed the only way to fix it would be something less than ideal... Had to ask anyway.

I always create the temp table first and then insert into.

I have heard that idea before... Is there a performance reason or just a code management guideline?

 

[gmmastros]

I suppose it's just a guideline that I follow. I probably read this (or something similar) a while ago, and then just got used to doing things this way.

[!]***[/!] Please note that this article specifically references SQL 6.5 and 7.

http://www.sql-server-performance.com/articles/per/avoid_bottlenecks_locking_tempdb_p1.aspx

Note the trick shown in that link.

Select ColumnList
Into   #TempTableName
From   YourTables
Where  1 = 0

This will create the table, then you can have your branching (if/else) to insert in to the table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Another interesting article:

*** References SQL Server 2008 R2

http://msdn.microsoft.com/en-us/library/ms188029.aspx

This article mentions some limitations of the Select Into syntax as well as a surprising benefit (minimally logged under certain conditions).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I am still cutting my T-SQL teeth...

If you post the actual code (as is, with the error), we may be able to offer an alternative method.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

The other thing that bit me is thinking the if construct has an end... see my OP.

I was thinking about blocks.... no need for a begin there <sigh>.

The code is too long to post, not to mention proprietary... The key differnece in the queries is a table in the from clause that deliberately has the same columns. It contains the projection data whereas the table it replaces contains the historical data.

The obvious alternative would be to push the data into the same table and use criteria instead but there is too much process around the other table to do that now. Plus the projections are still very ad hoc and I would not want to forget a key piece of criteria and blow all the non-projection data away.

I guess I could have also Unioned the two tables together, put the result in a temp table and used criteria for the projection... but that is going around the block to go in the front door.

Bah... No good answer... Still waiting for a project owner who gives me all the requirements up front.

 

[djj55]

On the drop are you using the "if exists"?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[lameid]

dj55....

No...

Although poking your suggestion for a few minutes I found this example in the drop table help...

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL 
DROP TABLE #temptable;

It also notes...

http://msdn.microsoft.com/en-us/library/ms173790.aspx[/URL]]
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

So in theory if I used the Go statement and used multiple batches it should work safely too.

I already used the option of creating the temp table first but this may have been the easier solution.

 

[gmmastros]

So in theory if I used the Go statement and used multiple batches it should work safely too.

Except that you cannot use GO within a stored procedure. You could do some dynamic stuff to use a go, but that would have been a lot worse (in my opinion) than creating the temp table first.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

And to think a few weeks ago I included a GO in a Stored Procedure a few weeks ago and had to take it out

I am good now... Temp table outside the insert into for the win.

 

[djj55]

By the way MS says a lot of things that are .... interesting.

George I have found to be a better reference. [Smile]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!