Problem with repeated use of temp tables

14 Sep 2006 5:26 PM

Dmitriy Antonov

Hello all,

Following is a sample script for sp:

Create Procedure spTest
     @Flag Bit
As
If @Flag=0 Begin
     Select *
     Into #tmp
     From tblTable1
End
Else Begin
     Select *
     Into #tmp
     From tblTable2
End

Select *
From #tmp

Drop Table #tmp
GO

This script will end up with an error:
"There is already an object named #tmp in the database."

Obviously, during run time there will be only one branch executed but
interpreter doesn't recognize this fact. Is there any work around this bug
(I believe it is a Microsoft's bug).

I am using SQL2000.

Thanks in advance,

Dmitriy.

14 Sep 2006 5:32 PM

Aaron Bertrand [SQL Server MVP]

It's not a bug that is going to be fixed -- it's that the parser doesn't
recognize that your code can only take one path or the other, and so balks
at trying to create the same #tmp table twice (even though we all know the
code could never try that).

Workarounds:

(a)
    if the schema of tblTable1 and tblTable2 are the same, then create the
#table first.

(b)
    dynamic SQL

DECLARE @sql NVARCHAR(2048);
SET @sql = 'SELECT * INTO #tmp FROM ' + CASE @Flag WHEN 0 THEN 'tblTable1'
ELSE 'tblTable2' END
    + '; SELECT * FROM #tmp; DROP TABLE #tmp;';
EXEC(@sql);

(Please read http://www.sommarskog.se/dynamic_sql.html)

(c)
    avoid the #tmp in the first place, why do you need it?

Show quote

"Dmitriy Antonov" <n*@no.com> wrote in message
news:%23egPOLC2GHA.4632@TK2MSFTNGP03.phx.gbl...
> Hello all,
>
> Following is a sample script for sp:
>
> Create Procedure spTest
>     @Flag Bit
> As
> If @Flag=0 Begin
>     Select *
>     Into #tmp
>     From tblTable1
> End
> Else Begin
>     Select *
>     Into #tmp
>     From tblTable2
> End
>
> Select *
> From #tmp
>
> Drop Table #tmp
> GO
>
> This script will end up with an error:
> "There is already an object named #tmp in the database."
>
> Obviously, during run time there will be only one branch executed but
> interpreter doesn't recognize this fact. Is there any work around this bug
> (I believe it is a Microsoft's bug).
>
> I am using SQL2000.
>
> Thanks in advance,
>
> Dmitriy.
>

14 Sep 2006 5:57 PM

Dmitriy Antonov

Thank you for your response, Aaron.

Unfortunately none of given workarounds will work for me. The sample in my
OP is just a sample to show the problem in its essence. After I get a
temporary table, I will need to apply a lot of updates in it before I return
it at the end as a dataset. And I return multiple datasets within one sp, so
the problem is multiplied accordingly.

(a) technically might work, but would require much more coding and
complicate future maintenance: changes in sp itself or, even worse, changes
in corresponding tables definitions.
(b) will not work because as I said I need to have an access to newly
created temp table - not just return it as a result. Did I miss something
here?
(c) Is there something better in this case?

And it is a bug - parser does prevent compiling of otherwise perfectly
working code isn't it a bug? Similar problem in any other programming
language would be considered as a bug, isn't it. Anyway I don't insist on
terminology. I just need some way, which would allow me to not turn one line
of code into entire page, which in addition, gives maintenance problems.

So still waiting for a better way (if possible).

Dmitriy.

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:enz3nOC2GHA.4312@TK2MSFTNGP02.phx.gbl...
> It's not a bug that is going to be fixed -- it's that the parser doesn't
> recognize that your code can only take one path or the other, and so balks
> at trying to create the same #tmp table twice (even though we all know the
> code could never try that).
>
> Workarounds:
>
> (a)
>    if the schema of tblTable1 and tblTable2 are the same, then create the
> #table first.
>
> (b)
>    dynamic SQL
>
> DECLARE @sql NVARCHAR(2048);
> SET @sql = 'SELECT * INTO #tmp FROM ' + CASE @Flag WHEN 0 THEN 'tblTable1'
> ELSE 'tblTable2' END
>    + '; SELECT * FROM #tmp; DROP TABLE #tmp;';
> EXEC(@sql);
>
> (Please read http://www.sommarskog.se/dynamic_sql.html)
>
> (c)
>    avoid the #tmp in the first place, why do you need it?
>
>
>
>
>
>
>
>
> "Dmitriy Antonov" <n*@no.com> wrote in message
> news:%23egPOLC2GHA.4632@TK2MSFTNGP03.phx.gbl...
>> Hello all,
>>
>> Following is a sample script for sp:
>>
>> Create Procedure spTest
>>     @Flag Bit
>> As
>> If @Flag=0 Begin
>>     Select *
>>     Into #tmp
>>     From tblTable1
>> End
>> Else Begin
>>     Select *
>>     Into #tmp
>>     From tblTable2
>> End
>>
>> Select *
>> From #tmp
>>
>> Drop Table #tmp
>> GO
>>
>> This script will end up with an error:
>> "There is already an object named #tmp in the database."
>>
>> Obviously, during run time there will be only one branch executed but
>> interpreter doesn't recognize this fact. Is there any work around this
>> bug (I believe it is a Microsoft's bug).
>>
>> I am using SQL2000.
>>
>> Thanks in advance,
>>
>> Dmitriy.
>>
>
>

14 Sep 2006 6:08 PM

Aaron Bertrand [SQL Server MVP]

> OP is just a sample to show the problem in its essence. After I get a
> temporary table, I will need to apply a lot of updates in it before I
> return it at the end as a dataset.

How are you going to apply updates to a table if you don't know its
structure?

> And I return multiple datasets within one sp,

WHY?

> (a) technically might work, but would require much more coding and
> complicate future maintenance: changes in sp itself or, even worse,
> changes in corresponding tables definitions.

You chose your path. A different path might be to create a different stored
procedure for the different types of processing.

> (b) will not work because as I said I need to have an access to newly
> created temp table - not just return it as a result. Did I miss something
> here?

I guess so. If you can say UPDATE #tmp SET foo = 1 you can equally do so
inside a dynamic SQL string.

> (c) Is there something better in this case?

Since I don't have any idea abotu the scope of the problem or why you need
to perform processing in a #tmp table, then sorry, no, I don't have any
suggestions for a better way to do things. If I see someone using a
toothbrush on the hood of their car, I am not going to suggest to them that
they should try it on their teeth instead. Maybe they're polishing the
hood, maybe they're trying to dislodge a chunk of food stuck in the
toothbrush.

> And it is a bug - parser does prevent compiling of otherwise perfectly
> working code isn't it a bug?

Maybe you should re-read my first sentence. I did not state that it wasn't
a bug.

> So still waiting for a better way (if possible).

I gave you three, now four workarounds. Thousands of people before you have
worked around this bug, since it has existed since I started using SQL
Server at least. If those workarounds aren't sufficient for you, then I
don't know what else to tell you. I can't fix SQL Server.

A