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.
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.