Use variable in table name question of Stored Procedures SQL Server 2000
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
sergionscarvalho@gmail.com
Mensagens
