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!

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.