CODEWITHSUNDEEP
reporting-services
aron
aron

Reputation: 2886

How to write SQL Query with an optional paramter?

I'm using SQL Server Reporting Services to make a few reports. I have a few reports where filters are optional.

For example .. Imagine you have a report of books. The end user has the option to specify (or filter by) none, one, or many authors.

I do not believe I can put an IF { } statement into my query?

If there a suggest or best way to do this in SQL Server Reporting Services? thanks!

Upvotes: 0

Views: 3718

Answers (3)

Mr Shoubs
Mr Shoubs

Reputation: 15389

You can do this anywhere you want a condition..

WHERE ((:param0 IS NULL) OR (column_name0 = :value0)) 
  AND ((:value1 IS NULL) OR (column_name1 = :value1)) 
  AND...

Pass Null into the parameter if you don't want to use it.

In SSRS, you'll need to set the paremeters as NULL by default in SSRS... see here for more detail

Upvotes: 4

Dan
Dan

Reputation: 1509

Its hard to tell without seeing the whole procedure that you've written, but you can have multiple values in your WHERE clause by using the statement IN,

eg:

SELECT
    *
FROM
   BOOKS 
WHERE
   AUTHOR IN ('AUTHOR1', 'AUTHOR2', ...)

You can provide everything in the author list in a single (type eg VARCHAR(MAX), TEXT) parameter if you like by using a set delimiter and then using a split function in your WHERE clause. Obviously if this parameter is empty or NULL the procedure would return all books.

There may be other approaches but this one works for me.

Upvotes: 3

Thomas
Thomas

Reputation: 64635

One solution is to push the requested authors into a staging table and query from that. 

Select ...
From books
Where Exists    (   
                Select 1
                From author_staging As S
                Where S.author = books.author
                )

If the desired behavior was to return all books if no authors were selected, then you could do:

Select ...
From books
Where Exists    (   
                Select 1
                From author_staging As S
                Where S.author = books.author
                Union All
                Select 1
                From ( Select 1 As Value ) As Z
                Where Not Exists    (
                                    Select 1
                                    From author_staging As S1
                                    )
                )

Or another form:

Select ...
From books
Where Exists    (   
                Select 1
                From author_staging As S
                Where S.author = books.author
                )
    Or Not Exists   (
                    Select 1
                    From author_staging As S1
                    )

Upvotes: 0

Related Questions