I have a dynamic query that I need to use to also reference a temp table. The temp table gets the most current year for each id passed in. I then need to query that table and get the data the most current year. The reason why the query is dynamic is because the fields being returned may vary.They query will not run because of the Temp table in it, but I cannot figure out how to get it to work right. Declare @TempTable Table ( ScoreYear varchar(4), ScoreSID varchar(15) ) Declare @strSQL varchar(max) --Inserts values into @TempTable Insert Into @TempTable(ScoreYear, ScoreSID) Select Max(ASY) as MaxYear, SID From Scores Where Scores.SID IN (Select * From SplitFunction(@SID,',')) Group By SID Set @strSQL = 'SELECT Student.FName, Student.LName, Student.SID, ' + @Fields + ' FROM Scores inner join PUSD.dbo.Student ON Scores.SID = Student.SID inner join + ' @TempTable + ' Temp on Scores.SID = Temp.SID and Scores.ASY = Temp.ScoreYear Where SID IN (' + (Select * From SplitFunction(@SID, ',')) + ')' Exec (@strSQL)This part:(Select * From SplitFunction(@SID, ',')is a function that puts the ID into a table format so it can be queried.PS The stored procedure is for Reporting Services.
Dynamic query with a Temp table
Posted - 2009-06-01 : 13:59:34
