Search code examples
sqlt-sqlreportssrs-2008

Can I use a report paramater as a section of the WHERE clause

I have a query that runs great as is like this 

WHERE (customer IN (@cust))

and @cust is a customer or a multiple list of customers

what I want to add is an AND with more in another optional parameter like 

No: @expiring = " "

OR

Yes: @expiring = " AND getDate() >= dateAdd(d,[expiryWarning],[expiryDate]) "

then I want to add that to the end of the WHERE and have it do that second part if it's chosen from the dropdown as Yes or No so I can show the whole list of customers or just the ones expiring in the report.

WHERE (customer IN (@cust)) @expiring

but I am seeing an error when I try to run this report

that there's an error near @expiring

any insight? I've been searching all day, is this even possible?

Solution

  • You need to change your where clause to take @expiring into account like this.

    WHERE customer in (@cust)
    AND (@expiring='No' OR getDate() >= dateAdd(d,[expiryWarning],[expiryDate]))

    So if @expring is 'No' then that part of the WHERE clause always returns true. If expiring = 'Yes' then the date criteria must be true else that part will return false.