Can we pass dynamic query in SSRS dataset?

Question

I have created an SSRS report with three parameters - Two mandatory parameters and third which is a search parameter and also allow blank/Null value. I have made a dynamic query to handle three situations for a that search parameter

1.) Display records without passing value in Third parameter i.e. NULL value.
2.) Display records by passing single value in Third parameter.
3.) Display records by passing multiple values in Third parameter.

I am using below query:

 IF (@Parameter3 = '')
 BEGIN
 EXEC('SELECT  * FROM [TblName] with(nolock)
 where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+')
 ') 
 END 
 ELSE
BEGIN 
EXEC ('SELECT  * FROM [TblName] with(nolock)
where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+') and [Col3]  ('+@Parameter3+')  
')
END

Now when I executes this query in sql server after passing my above three different scenerios it gives result acoording to parameters passed, But when I tried to add this in my Report Dataset it gives error-- Can a dynamic query be added into a dataset in SSRS?

The answer is "Yes", however you're really asking why it doesn't work. For help, tou will need to provide the SQL query that isn't working, the error you get (including the parameters passed to cause the error), under what conditions does that error occur, and perhaps a screen shot of your parameter page? — BIDeveloper
I am Using this query: IF (@Parameter3 = '') BEGIN EXEC('SELECT * FROM [TblName] with(nolock) where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+') ') END ELSE BEGIN EXEC ('SELECT * FROM [TblName] with(nolock) where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+') and [Col3] ('+@Parameter3+') ') END — user2728106
Can you edit your original question with : you will need to provide the SQL query that isn't working, the error you get (including the parameters passed to cause the error), under what conditions does that error occur, and perhaps a screen shot of your parameter page? — BIDeveloper

R. Richards R. Richards · Accepted Answer · 2016-06-29T11:57:35

Your SQL is missing an IN clause after Col3.

IF (@Parameter3 = '')
BEGIN
    EXEC('SELECT  * FROM [TblName] WITH (NOLOCK)
    where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+')
    ') 
END 
ELSE
BEGIN 
    EXEC ('SELECT  * FROM [TblName] WITH (NOLOCK)
    where [Col1] in ('+@Parameter1+') and [Col2] in ('+@Parameter2+') and [Col3] in ('+@Parameter3+')  
    ')
END

Without knowing anything about the datatypes for your parameters, this should work. Expect this to be problematic when more than one value is chosen in a parameter.

I am not sure that the SQL needs to be dynamic. The code below could work just as well.

IF (@Parameter3 = '')
BEGIN
    SELECT  * FROM [TblName] WITH (NOLOCK)
    WHERE [Col1] IN (@Parameter1) 
    AND [Col2] IN (@Parameter2) 
END 
ELSE
BEGIN 
    SELECT  * FROM [TblName] WITH (NOLOCK)
    WHERE [Col1] IN (@Parameter1) 
    AND [Col2] IN (@Parameter2) 
    AND [Col3] IN (@Parameter3) 
END

Good luck!

Can we pass dynamic query in SSRS dataset?

1 Answers