SSRS: Change SQL Statement Dynamically

reporting-services, reportingservices-2005, sql-server, t-sql

Solution

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.

Problem

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!

Original source