I am trying to create a CTE dynamically in a stored procedure using the results from another SP that is being called. I don't know the columns that will be returned from the called SP since it varies based on the data passed to it. Here is an example of my SQL (Note: The #tempResultsWins table is only there now as a placeholder, it is the table I am trying to create using the results from the 'tempResultsWins' SP):
declare @team int = 451;
declare @TimePeriod int = 1;
declare @RS int = 1;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #tempResultsWins ([Season] [int] ,
[Division_Name] varchar(150),
[conf] varchar(20));
CREATE TABLE #tempResults (TimeZoneName varchar(150), Order_By smallint);
INSERT INTO #tempResults EXEC usp_TimeZonesTeam @Team=@Team, @RS=@RS;
Declare @Productlist1 varchar(250), @Productlist2 varchar(250);
set @Productlist1 =SUBSTRING(( select ',['+TimeZoneName+']' from #tempResults ORDER BY Order_By for xml path('')),2,8000)
set @Productlist2 =SUBSTRING(( select ',IsNull(['+TimeZoneName+'],0) as ['+TimeZoneName+']' from #tempResults ORDER BY Order_By for xml path('')),2,8000)
INSERT INTO #tempResultsWins EXEC TimeZoneStandingsTeamWins @team=@team, @TimePeriod=@TimePeriod,
@Productlist1=@Productlist1, @Productlist2=@Productlist2, @RS=@RS;
select #tempResultsWins.* from #tempResultsWins;
DROP TABLE #tempResults;
DROP TABLE #tempResultsWins
;
The point of all this is that in reality there will be two temporary tables that are the results of calling two SP. The statement "select #tempResultsWins.* from #tempResultsWins;" will actually be:
select #tempResultsWins.*, #tempResultsLoss.*
from #tempResultsWins
inner join #tempResultsLoss
on #tempResultsWins.Season = #tempResultsLoss.Season