CODEWITHSUNDEEP
sqlt-sqlreportssrs-2008
ScottC
ScottC

Reputation: 462

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?

Upvotes: 0

Views: 33

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

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.

Upvotes: 2

Related Questions