SSRS: changing a SQL query dynamically

Question

I have a report in SSRS 2005 based on a query that looks like this:

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

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

Answer 1

Charles answered almost correctly.

It should be:

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

This is the classic "pattern" in SQL for conditional predicates. If @checked = 0 , then it will return all rows corresponding to the rest of the predicate ( col1 = 'ABC' ). SQL Server doesn't even handle the second half of OR .

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

If you have several conditional predicates, you can bind them together using this method (while the IF and CASE methods will quickly become unmanageable).

For instance:

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

Do not use dynamic SQL, do not use IF or CASE.

Answer 2

How about this one. @checked is your bit variable.

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

Edit: Also, if you are not using a saved process, use it.

Answer 3

Maybe this will 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%' 

Sorry, I don't use SSRS a lot, but if you can set the checkbox value to the @checked parameter, this should work.

Alternatively, you can use the CASE WHEN statement.

Answer 4

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

Answer 5

This will work in SSRS 2000, but is used as a last resort.

(bad) PSEUDOCODE

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

Departure Performing "dynamic" SQL queries . from the Hitchhiker SQL Server 2000 Reporting Services Reference

Answer 6

One way to do this is to create an SSRS query as an expression. In the BIDS report designer, specify the query as follows:

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

Answer 7

You can also use a different 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 quoted text.

Answer 8

if you can use stored procedures, it is probably easier to do there. go through your options. Create an SQL string based on your conditions and execute EXEC on the sql string, your saved proc will return the results you need.