SSRS dynamic dataset

Question

I have created dynamic dataset, but I am not able to go forward with the parameterName comparison in the dataset.

declare @tableName varchar(100);

SELECT [Name] as column1 ,[Definition] as column2 ,'View' as column3 FROM @tableName union all SELECT
[DayOfWeek] as column1 ,[MonthName] as column2 ,'common' as column3
FROM @tableName

So, this SQL works perfectly fine if I hardcode the tableName but when I pass parameter, it breaks. also, See the column3, this column actually differentiates the dataset based on the schema name, so that I can compare this schema name from a resultset(dataset) and then based on the @tableName parameter I should be able to select data for only one table.

So, I have two problems 1. Passing a dynamic tableName as parameter 2. comparing this ParameterName in the where Clause to select specific data from my dynamic dataset. Please help me.

Naveen Kumar Naveen Kumar · Accepted Answer · 2015-07-27T13:35:24

you can try with below script to create DataSet Declare a table type variable & insert result set .. once its succeed you can extend your script by adding filter criteria... All the Best!

DECLARE @tableName VARCHAR(100)='Test';
DECLARE @DynamicTable AS Table(column1  VARCHAR(255),column2  VARCHAR(255),column3  VARCHAR(255))
DECLARE @Query AS VARCHAR(MAX)=''
SET @Query='
    SELECT [NAME] AS COLUMN1 ,[DEFINITION] AS COLUMN2 ,''VIEW'' AS COLUMN3 FROM ' + @TABLENAME 
    +
    ' 
    UNION ALL 
    SELECT
    [DAYOFWEEK] AS COLUMN1 ,[MONTHNAME] AS COLUMN2 ,''COMMON'' AS COLUMN3
    FROM ' + @TABLENAME

--PRINT @Query
Insert Into @DynamicTable 
EXEC (@Query)
Select * From @DynamicTable

SSRS dynamic dataset

1 Answers