SQL Switch Case in where clause

0 votes

I tried searching around, but I couldn't find anything that would help me out.

I'm trying to do this in SQL:

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
CASE @locationType
    WHEN 'location' THEN account_location = @locationID
    WHEN 'area' THEN xxx_location_area = @locationID
    WHEN 'division' THEN xxx_location_division = @locationID

I know that I shouldn't have to put '= @locationID' at the end of each one, but I can't get the syntax even close to being correct. SQL keeps complaining about my '=' on the first WHEN line...

How can I do this?

Feb 7 in Database by Neha
• 8,940 points
209 views

1 answer to this question.

0 votes

Without a case statement:

SELECT
   column1, 
   column2
FROM
   viewWhatever
WHERE
CASE 
    WHEN @locationType = 'location' AND account_location = @locationID THEN 1
    WHEN @locationType = 'area' AND xxx_location_area = @locationID THEN 1
    WHEN @locationType = 'division' AND xxx_location_division = @locationID THEN 1
    ELSE 0
END = 1

OR

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
  END
answered Feb 7 by Vaani
• 7,020 points

Related Questions In Database

0 votes
0 answers

"CASE" statement within "WHERE" clause in SQL Server 2008

The query I'm working with has a ...READ MORE

Sep 3 in Database by Kithuzzz
• 20,660 points
54 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
76 views
0 votes
0 answers

How to Return Multiple Values from CASE clause in Where Condition

I have a condition where utilizing the ...READ MORE

Aug 25 in Database by Kithuzzz
• 20,660 points
100 views
0 votes
1 answer

Clause in SQL

SQL clause helps to limit the result ...READ MORE

answered Oct 8, 2018 in Database by DataKing99
• 8,240 points
256 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9 in Database by Kithuzzz
• 20,660 points
72 views
0 votes
1 answer

IN vs OR in the SQL WHERE Clause

I assume you want to know the ...READ MORE

answered Sep 24, 2018 in Database by DataKing99
• 8,240 points
730 views
0 votes
0 answers

How to use the where clause in R programming?

I'm trying to implement a where clause ...READ MORE

Dec 24, 2018 in Data Analytics by Sophie may
• 10,590 points
3,268 views
0 votes
1 answer

What is the rule to use group by, having and where clause?

Hi samar, this is a very common mistake ...READ MORE

answered Jul 2, 2019 in Database by anonymous
687 views
0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Two forms of CASE statements are getting ...READ MORE

answered Feb 8 in Database by Vaani
• 7,020 points
128 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17 in Database by Vaani
• 7,020 points
122 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP