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

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.

Answer 2

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.

Answer 3

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.

Answer 4

Beware the answer

How about this. @checked is your bit variable.

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.
(Sorry I don't have enough rep to downvote)

Answer 5

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

Answer 6

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

Answer 7

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%'","")

Answer 8

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.

Answer 9

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.