SQL Switch/Case in where clause

Question

I tried searching around but 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...

Help!

Thanks in advance

Answer 1

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

Answer 2

I'd say this is an indicator of a flawed table structure. Perhaps the different location types should be separated in different tables, enabling you to do much richer querying and also avoid having superfluous columns around.

If you're unable to change the structure, something like the below might work:

SELECT
    *
FROM
    Test
WHERE
    Account_Location = CASE LocationType
         WHEN 'location' THEN @locationID
         ELSE Account_Location END
    AND
    Account_Location_Area = CASE LocationType
          WHEN 'area' THEN @locationID
          ELSE Account_Location_Area END

And so forth... We can't change the structure of the query on the fly, but we can override it by making the predicates equal themselves out.

EDIT: The above suggestions are of course much better, just ignore mine.

Answer 3

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

Answer 4

I'm still in the same boat... I tried this (i guess i'll show my real column names, argh!):

select @msgID, account_id
from viewMailAccountsHeirachy
where @smartLocationType = 
CASE 
    WHEN 'store' THEN account_location = @smartLocation
    WHEN 'area' THEN xxx_location_area = @smartLocation
    WHEN 'division' THEN xxx_location_division = @smartLocation
    WHEN 'company' THEN xxx_location_company = @smartLocation
END

and i get this error from SQL:

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

Its telling me the problem is the first WHEN line...

FYI, this is all happening in a sproc, i just didn't want to have to make 4 separate select statements (I'm actually inserting these into a table right above)

Answer 5

Here you go.

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

Answer 6

without a case statement...

WHERE (@locationType = 'location' AND account_location = @locationID) or (@locationType = 'area' AND xxx_location_area = @locationID) or (@locationType = 'division' AND xxx_location_division = @locationID)

Answer 7

While reading on SWITCH CASE I an interesting areticle..have a look..

http://www.a2zmenu.com/MySql/Using%20SQL%20SWITCH%20CASE%20Statement.aspx