Can temp table created in a string be made available outside of string

[tokuzumi]

I have a stored procedure, that I build a temp table, using a string. The reason for building the temp table in a string, is I have a dynamic number of columns, depending on how many metrics a user selects. This string built table is the first of many temp tables to be utilized in this stored procedure. But I can't call the temp table outside of the string, as it gives an error message, "Invalid Object: #table1 (name of temp table)" I don't know if I can keep the entire stored proc under 8000 characters, and I tried making the string a text datatype, but I can't use the text, ntext, or image data types on variables.

Here is a sample of how my procedure works (column names have been changed to protect the innocent):

declare @String1 varchar(8000), @m int
set @m = 1

set @string1 = '
create table #table1
(
FirstCol int,
SecCol int,
ThirdCol int,
FourthCol int,
FifthCol float,
SixthCol float '
while @m <= @NumofSelectedMetrics
begin
set @string1 = @string1 + ', Col' + cast(@m as varchar(23)) + ' float'
set @m = @m + 1
end
set @string1 = @string1 + ')'

and the procedure goes on from there. Any ideas on how to call the temp table created in the string outside of the string? Thanks for any help.

 

[royjimenez]

I think that u should make a new table for keeping the cols that u add with dynamic cursor.

To execute u must use

exec ( @string1 )


I hope to understand u.

Regards.

 

[tokuzumi]

I do use the exec (@String1) statement at the end of the procedure. The temp table I use works perfectly. The only problem I have is when I try to call the temp table after I have executed the string.

declare @string1 varchar(8000)

set @string1 = '
create table #table1
(
standard columns
loop to do unknown columns
)
insert into #table1
select standard fields, loop to get values for unknown columns'

exec (@string1)

if I try to use #table1, I get the error &quot;Invalid Object: #table1&quot;

 

[JamesLean]

When you create a local temp table, it is only available to the context that it was created in. When you use dynamic SQL, it runs in its own context - NOT that of the calling stored proc. That is why when you try and access the table from the SP after creating it using EXEC, you can't see it.

You could try using a global temp table (##table) but remember if two people try and run the SP at the same time, you will get an error.

If you can't do this then you may have to rethink how you build this table.

--James