Case statement in where clause with parameters

Question

I have been banging my head with this.

basically...

where....
and PaymentType IN CASE WHEN @PmtType = 'B' THEN ('BizPay','PerPay')
                        WHEN @PmtType = 'Z' THEN ('BizPay')
                        WHEN @PmtType = 'P' THEN ('PerPay')
                        ELSE NULL END

is what I'm after. I've tried rearrangeing the CASE, putting it in parenthesis, all over here and google, I'm just not getting it to work. Any help greatly appreacaited. Future thanks.

possible duplicate of [Using CASE Statement inside IN Clause](http://stackoverflow.com/questions/11232267/using-case-statement-inside-in-clause) — Raphaël Althaus, Mar 26 '14 at 15:47
Thanks, I had read that question earilier but didn't quite think it would apply to my situation. Before bitching about referring me to something I already read I took your suggestion, read it again carefully and got it working AND I think I understand it. Thanks again. :) — mrbungle, Mar 26 '14 at 16:13

Pragnesh Khalas · Answer 1 · 2014-03-26T16:16:54.243

0

You can use like this

   and PaymentType =CASE WHEN @PmtType = 'B' THEN 
        CASE WHEN PaymentType IN ('BizPay','PerPay') THEN PaymentType ELSE '-1' END
                    WHEN @PmtType = 'Z' THEN
                      CASE WHEN PaymentType IN  ('BizPay') THEN PaymentType ELSE '-1' END                        
                    WHEN @PmtType = 'P' THEN  
          CASE WHEN PaymentType IN  ('PerPay') THEN PaymentType ELSE '-1' END
                    ELSE NULL END

edited Mar 26 '14 at 16:16

answered Mar 26 '14 at 15:49

Pragnesh Khalas

2,908
2
13
26

This almost works but I get a syntax error on the comma between ('BizPay','PerPay') – mrbungle Mar 26 '14 at 15:56

score 0 · Answer 2 · answered Mar 26 '14 at 16:18

0

to anyone out there wondering here was my solution

AND  (@PmtType = 'B' and PaymentType in ('BizPay','PerPay'))
          or (@PmtType = 'Z'  and PaymentType in ('BizPay'))
          or (@PmtType = 'P' and PaymentType in ('PerPay'))

answered Mar 26 '14 at 16:18

mrbungle

1,921
1
16
27

Case statement in where clause with parameters

2 Answers2