CODEWITHSUNDEEP
sql-serverreporting-services
Chris The DBA
Chris The DBA

Reputation: 105

Can't figure out the syntax for a where clause

I have a report that contains the following parameter "Fine > 0" and the choices are "Yes", "No" and both using a multi-select dropdown box. The parameter gets passed to the stored procedure as either "Yes', "No" or "Yes, No".

In the SP has syntax similar to this

Case WHEN @FineAmount = 'Yes' THEN I.Fine > 0 
WHEN @FineAmount = 'No' Then I.Fine = 0
ELSE I.Fine >= 0
    END

Upvotes: 0

Views: 35

Answers (1)

Derek
Derek

Reputation: 23228

Here's some case logic that will get the job done. 

where
  I.Fine > case
    when @FineAmount like '%Yes%' then 0
    else I.Fine + 1 end
  or I.Fine = case
    when @FineAmount like '%No%' then 0
    else I.Fine - 1 end

Upvotes: 2

Related Questions