SQLTeam.com | Weblogs | Forums

Creating a CTE dynamically from the results of a called stored procedure

sql2014

#1

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

#2

Can you post an example of a CTE that this procedure would generate?


#3

It depends on the value of "@team" that is passed to the "TimeZoneStandingsTeamWins" SP, since each team could have played in different time zones. For example the SP could return Eastern, Western, Central, Pacific (all "int" values) or it could return those plus "Europe" and/or "Asia" as additional "int" values.


#4

TimeZoneStandingsTeamWins is doing a pivot, with the columns being the various time zones and the values being the number of wins in each one.


#5

The two easiest methods would be to either change the stored procedure to a function or use some dynamic SQL to use OPENROWSET to do a SELECT INTO on a global temp table. Of course, both have caveats. The right way to do it would be to rewrite the stored procedure and the underlying tables so that you can still get the correct functionality from it without having to deal with dynamic columns. Of course, no one wants to bite that particular bullet.