Hello
I’m constructing a SQL string in a stored procedure (see below). The value of the input parameter (@pClass) determines what fields are filtered in the WHERE clause.
I want to use the procedure in SSRS, but when I create the data set , there are no fields showing, so I’m dumping the data into a test table.
Is there a way to get the fields out without using the test table?
Thanks in advance
Ron
@pClass VARCHAR(120),
AS
DROP TABLE IF EXISTS dbo.tblTest
SET @SQL = ' SELECT * into dbo.tblTest From
(Variable SQL clause in
SELECT * FROM Student l
INNER JOIN dbo.StudentDetails f ON l.StudentKey=f.StudentKey
INNER JOIN [dbo].[StudentStatus] ps ON ps.StudentKey=f.StudentKey
INNER JOIN dbo.Date d ON d.DateKey=f.StartDateKey
INNER JOIN dbo.StudentAccount a ON a.AccountKey=f.AccountKey
WHERE a.StudentAccLevel= '''+@pClass +''''
SET @SQL=@SQL+ Case @pClass
WHEN ABC THEN ' AND d.Fied1Z=1 '
WHEN'DEF' THEN ' AND ((d.FieldB=1) OR (d.FieldC=1)) '
ELSE ' AND ((d.PreviousCompleteMonth=1) OR (d.Previous2ndCompleteMonth=1)) '
END
SET @SQL=@SQL+' ) As a'
EXECUTE sp_executesql @SQL
SELECT * FROM dbo.tblTest