I'm, sorry

SELECT from temp table must be on end of @sql or you can ommit into #temp

MartinaJ

set @SQL = 
'
select * INTO #TEMP from ( 

SELECT 5 AS myorder, 
''AltModeParticipation'' as Label, 
[Year],
AltModeParticipation 
FROM HistoricalSummary 
WHERE CompanyName = @CompanyName
) Table1 
pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 ;
select * from #temp
'	
execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;	

>Thanks Martina -
>That's exactly what I was trying to do. The code will run without the last select, but with it there is an error that says "Invalid object name #TEMP".
>
>>Hi Don,
>>
>>

>>set @SQL = '
>>select *  INTO #TEMP  from ( 
>>SELECT 5 AS myorder, 
>>''AltModeParticipation'' as Label, 
>>[Year],
>>AltModeParticipation 
>>FROM HistoricalSummary 
>>WHERE CompanyName = @CompanyName
>>) Table1 
>>pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 
>>'	
>>execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;
>><strong>SELECT * FROM #TEMP</strong>
>>

>>
>>MartinaJ
>>
>>>Hi, I'm still trying to find a best solution to this. I have this simplified query that works OK.
>>>

>>>set @SQL = 
>>>'
>>>select * INTO #TEMP from ( 
>>>
>>>SELECT 5 AS myorder, 
>>>''AltModeParticipation'' as Label, 
>>>[Year],
>>>AltModeParticipation 
>>>FROM HistoricalSummary 
>>>WHERE CompanyName = @CompanyName
>>>) Table1 
>>>pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 '	
>>>	
>>>execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;	

>>>
>>>However, when I try to access #TEMP with a followup select or insert I get an error that the object does not exist. Since it seems the original query executes OK, what happens to the tempory table? I assume what is happening is that the query is creating a temp connection that is closed after the execute and taking #TEMP with it. Does anyone know how I can hang onto it?
>>>
>>>Thanks