SSRS: Change SQL Statement Dynamically

Question

I have a report in SSRS 2005 that's based on a query that's similar to this one:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
AND col2 LIKE '%XYZ%'

I need to be able to dynamically include the AND part of the WHERE clause in the query based on whether the user has checked a checkbox. Basically, this is a dynamic SQL statement and that's the problem. I tried several approaches to no avail. Is this possible? Does SSRS 2005 supports dynamic SQL? Thanks!

Colitis

Answer 1

Charles almost had the correct answer.

It should be:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
   AND (@checked = 0 OR col2 LIKE '%XYZ%')

This is a classic "pattern" in SQL for conditional predicates. If @checked = 0, then it will return all rows matching the remainder of the predicate (col1 = 'ABC'). SQL Server won't even process the second half of the OR.

If @checked = 1 then it will evaluate the second part of the OR and return rows matching col1 = 'ABC' AND col2 LIKE '%XYZ%'

If you have multiple conditional predicates they can be chained together using this method (while the IF and CASE methods would quickly become unmanageable).

For example:

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
    AND (@checked1 = 0 OR col2 LIKE '%XYZ%')
    AND (@checked2 = 0 OR col3 LIKE '%MNO%')

Don't use dynamic SQL, don't use IF or CASE.

Pahl

Yep, I can't abide dynamic SQL, impossibly hard to debug, maintain or upgrade. Some minor performance improvements if you have large numbers of optional parameters, but even with up to 30 parameters I got good performace without resorting to it. – Hydrostatic

Not to mention how dangerous it is, from a security PoV – Thermostatics

Excellent answer – Audit

Answer 2

How about this. @checked is your bit variable.

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
AND (@checked <> 0 and col2 LIKE '%XYZ%')

Edit: Also, if you aren't using a stored proc, then use one.

Stilla

It doesn't work in the case where checked is false (0). It will not return any matches in this case, even though some may exist. – Crossbred

Your answer is great but in the case where we can't execute on the data source and we have read-only access to data the strored procedure approach won't work. – Hyperextension

Answer 3

Perhaps this would work for you:

if @checked = 1
    select * from mytable (nolock) where col = 'ABC'
else
    select * from mytable (nolock) where col = 'ABC' AND colw Like '%XYZ%'

I'm sorry I don't use SSRS much, but if you can get the value of the checkbox into the @checked parameter this should work.

Alternately you could use a CASE WHEN statement.

Wimple

In SQL Server this can cause query plan recompilation, which can hurt performance. If you use this pattern in your own procedures, consider splitting so that you have the master procedure, which chooses which child procedure to call based on parameters - One procedure one query. – Hydrostatic

Answer 4

SELECT * FROM MyTable (NOLOCK) 
WHERE col1 = 'ABC'
AND col2 LIKE CASE @checked WHEN 1 THEN '%XYZ%' ELSE col2 END

Sclerotic

Answer 5

This would work in SSRS 2000 but used as a last resort.

(bad) PSEUDOCODE

="SELECT * FROM MyTable (NOLOCK)
WHERE col1 = 'ABC'"+
iff(condition,true,"AND col2 LIKE '%XYZ%'","")

Check out Executing "Dynamic" SQL Queries. from the Hitchhiker's Guide to SQL Server 2000 Reporting Services

Eft

Answer 6

One way to do this is by generating the SSRS query as an expression. In the BIDS report designer, set your query up like so:

="SELECT * FROM MyTable WITH (NOLOCK) WHERE col1 = 'ABC'" +
 Iif(Parameters!Checked.Value = true," AND col2 LIKE '%XYZ%'","")

Spillman

Answer 7

You can also take another approach and use the Exec function:

DECLARE @CommonSelectText varchar(2000)  
DECLARE @CompleteSelectText varchar(2000)  
SELECT @CommonSelectText = 'SELECT * FROM MyTable (nolock) Where Col = ''ABC'' '  

IF @checked = 1   
    SELECT @CompleteSelectText = @CommonSelectText + ' AND ColW LIKE ''%XYZ%'' '  
ELSE  
    SELECT @CompleteSelectText = @CommonSelectText  

EXEC (@CompleteSelectText)  

GO  

Note the use of two apostrophes ' to mark the quoted text.

Crossbred

Answer 8

if you can use stored procedures, its probably easier to do it there. pass in your params. Create a SQL String based on your conditions and do an EXEC on the sql string, your stored proc will return the results you need.

Zingg

Your answer is great but in the case where we can't execute on the data source and we have read-only access to data the strored procedure approach won't work. – Hyperextension