CODEWITHSUNDEEP
sql
cricket49
cricket49

Reputation: 45

sql Where equal to or not in with one parameter

I want to pass a parameter to a procedure that is used for a country with possible values of 'ALL', 'OTHER', or specific country abbreviations like 'HK', or 'TW'. 'OTHER' should select all countries that aren't specifically listed (in this case 'HK', 'TW').

The only way I can currently think of is to do it as two separate queries using IF/ELSE. 

IF (@i_Country = 'OTHER')
   SELECT * FROM table WHERE Country NOT IN ('HK', 'TW')
ELSE
   SELECT * FROM table WHERE Country LIKE
   CASE 
      WHEN @i_Country = 'ALL' THEN '%'
      ELSE @i_Country
   END

Is it possible to put this neatly in the WHERE of one query without using IF/ELSE?

Upvotes: 0

Views: 176

Answers (1)

Nate Anderson
Nate Anderson

Reputation: 690

Can you give this a shot, since I can't test it without the table.

SELECT * 
FROM [table] 
WHERE (@i_Country = 'OTHER' AND Country NOT IN ('HK', 'TW')) 
OR (@i_Country <> 'ALL' AND @i_Country <> 'OTHER' AND Country = @i_Country)
OR (@i_Country = 'ALL')

Upvotes: 1

Related Questions