SQL Switch/Case in 'where' clause

sql switch-statement case
Miles picture Miles · Oct 15, 2008 · Viewed 613.9k times · Source

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?

Answer

Bob Probst picture Bob Probst · Oct 15, 2008 
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

Related questions

How do I use properly CASE..WHEN in MySQL

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3: SELECT CASE course_enrollment_settings.base_price WHEN course_enrollment_settings.base_price = 0 THEN 1 WHEN course_enrollment_settings.…

Read More
SQL use CASE statement in WHERE IN clause

Is it posible to use case in where in clause? Something like this: DECLARE @Status VARCHAR(50); SET @Status='published'; SELECT * FROM Product P WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3) WHEN @Status='standby' THEN (2,5,9,6) WHEN @Status='deleted' THEN (4,5,8,10) …

Read More
SWITCH with LIKE inside SELECT query in MySQL

I have this Tags table CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `…

Read More