CODEWITHSUNDEEP
sqlstored-procedures
kostas.kapasakis
kostas.kapasakis

Reputation: 1142

Case inside 'where' section that changes the condition of an AND statement

I am creating a store procedure and i am wondering how can i add a case block in an Add statement inside the where statement.That case statement checks an input parameter and depending its value it will change the condition from greater that to smaller than and of course be added to the add conditions

So a part of the query is like:

WHERE 
AND BM.Example1 IS NOT NULL
AND BM.Example2 IS NOT NULL
AND ( Case  When @inputParamter= 'A'  THEN   AND BM.Example < 0 ELSE And BM.Example> 0 )
ORDER BY 'SEG' ASC, 'CCY' ASC

So by this approach i am thinking to extract an add statement depending on the input parameter but unfortunately i keep getting syntax errors.

Is that possible?

Upvotes: 1

Views: 48

Answers (2)

Kostya
Kostya

Reputation: 1605

here is a similar answer using case 

AND ( Case  When @inputParamter = 'A'  AND BM.Example < 0 THEN 'Y'
            When @inputParamter <> 'A'  AND BM.Example > 0 THEN 'Y' ELSE 'N' END = 'Y')

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

Yepp, just use this:

AND (( @inputParamter= 'A' AND BM.Example < 0) OR ( @inputParamter<>'A' AND BM.Example> 0) )

However, be carefull with NULL, you have to put it in the logic as a third option.

Upvotes: 2

Related Questions