CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user710502
user710502

Reputation: 11469

How to evaluate an input in the WHERE clause

I am getting an input in one of my variables and basically I want to do something like

SELECT * FROM PEOPLE
WHERE 
     IF @INPUT = 1
         ITEMID = 16 OR ITEMID = 13
     ELSE IF @INPUT = 2
         ITEMID = 11 OR ITEMID = 14
     ELSE
         ITEMID = 0

Is there a way to do this, please forgive if this is wrong but I wanted to portray my issue as easily understandable as possible.

I tried with CASE before this like

WHERE 
    CASE @INPUT
       WHEN 1 THEN ITEMID = 16 OR ITEMID = 13
       WHEN 2 THEN ITEMID = 11 OR ITEMID = 14
       ELSE ITEMID = 0
    END

But all of these approaches throw errors, I would appreciate a good advise, more efficient way to do this if possible.

Thank you for the help.

Upvotes: 3

Views: 114

Answers (3)

mrmillsy
mrmillsy

Reputation: 495

Not to obfuscate the issue TOO much, but I will just quickly point out that in my view Cory, when he suggests a new column, is on the right track, but I can understand that you don't always have the freedom to change your data structures.

However, if I can't change my data structures I'd still prefer to have my SELECT statement made in such a way as though I did have the perfect structure to work from.

For this I tend to use APPLY to add an extra derived column to which I can refer in my WHERE or SELECT clauses.

Eg:

SELECT
    *
FROM People p
    CROSS APPLY (
        SELECT
            CASE
                WHEN p.ItemID IN (13,16) THEN 1
                WHEN p.ItemID IN (11,14) THEN 2
                ELSE 0
            END        AS ItemCode
    ) pInfo
WHERE
    @Input = pInfo.ItemCode

But really I just like using CROSS APPLY too much.

This does have the advantage that if you ever want to re-use that conditional logic (for example by adding SELECT pInfo.ItemCode, or even GROUP BY pInfo.ItemCode) then it's all contained in one place.

Further, if you do ever add that column which Cory mentioned, any places in the query where you have used the ItemCode data are already optimised for the new structure you create.

Upvotes: 0

AlexDev
AlexDev

Reputation: 4717

Just to give another answer..

WHERE (@INPUT in (1,2) AND ITEMID+2*@INPUT IN (15,18))
OR (@INPUT NOT IN (1,2) AND ITEMID = 0)

or

WHERE (@INPUT in (1,2) AND ITEMID IN (15-2*@INPUT,18-2*@INPUT))
OR (@INPUT NOT IN (1,2) AND ITEMID = 0)

Works because when @input = 1:

  • if ITEMID = 13, ITEMID + 2*1 = 15
  • if ITEMID = 16, ITEMID + 2*1 = 18

and when @input = 2:

  • if ITEMID = 11, ITEMID + 2*2 = 15
  • if ITEMID = 14, ITEMID + 2*2 = 18

But you should never use this, unless you intentionally want to obscure your code!

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32710

 WHERE (@INPUT = 1 AND ITEMID IN (13,16)) 
    OR (@INPUT = 2 AND ITEMID IN (11,14))
    OR (@INPUT NOT IN (1,2) AND ITEMID = 0)

Upvotes: 6

Related Questions