sc-NET - Blogger, Programação, Internet em Geral

I have one Stored procedure that use a global temporary table, because if I use a local temporary table, give error, because is created with the result of a SQL in Linked server:

EXEC('SELECT * INTO ##TABLE FROM OPENQUERY( LINKEDSERVER, ''SELECT CP1, CP2, CP3 FROM TABLEOTHERSERVER WHERE CP1 = '+@CODE+''')')

and then i use the temporary table in the next line

FOR SELECT CP1, CP2, CP3 FROM ##TABLE ORDER BY CP1

If I use local temporary table give erro in the previous line to define the table #TABLE, because this I use ## global temporary tables

AND THE PROBLEM IS

The stored procedure runs simultaneously in diferent connections and give the error that the table ##TABLE is already created, the soluction is create unique global temporary tables with the help of one parameter that I receive in Stored Procedure, the first line look like:

EXEC('SELECT * INTO ##TABLE'+@PARAMETER+' FROM OPENQUERY( LINKEDSERVER, ''SELECT CP1, CP2, CP3 FROM TABLEOTHERSERVER WHERE CP1 = '+@CODE+''')')

in this case the name of the global temporary table is for example ##TABLE0001

but in the other line:

FOR SELECT CP1, CP2, CP3 FROM ##TABLE+@PARAMETER ORDER BY CP1

give-me error if I try to concatenate the @PARAMETER to the rest of the table name.

Any ideas ??

 

Post nos newsgroups da Microsoft em Inglês http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&mid=0c62e450-fcd9-48ff-8f67-2f14c8ffc32b

Post nos newsgroups da Microsoft em Português http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.pt.sql&cat=pt_PT_001633e6-47b3-460b-a215-1a096bfab972&lang=pt&cr=PT&mid=9bf12d67-1ee0-4b34-ad00-dd8c79c6fb20