SQL Switch/Case in 'where' clause - Stack Overflow

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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?
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
                As TomH noted in the comment to your reply below, you formed the SQL incorrectly.  I tested mine in SQLServer 2005 and it worked fine.
– Bob Probst
                Oct 16, 2008 at 0:48
                @ indicates variables in t-sql, without the @ , @locationID would be interpreted as a column name.
– Christian Sloper
                Mar 13, 2018 at 10:08
    (@locationType = 'location' AND account_location = @locationID)
    (@locationType = 'area' AND xxx_location_area = @locationID)
    (@locationType = 'division' AND xxx_location_division = @locationID)
                This is will give a slightly different result as the Case statement exits after a condition is met - but the OR syntax will evaluate all the possibilities
– tember
                May 12, 2015 at 18:27
                @tember Even if SQL were a procedural language, if the first OR is true then the rest of the expression doesn't get evaluated. Since SQL is a declarative language, how do you know the DBM will evaluated all the ORs? Sincere question, I don't understand.
– ArturoTena
                Jun 19, 2015 at 20:25
                In SQL the rest of the expression does get evaluated in the OR syntax.  Try this (it wouldn't let me include the @ symbol - you will have to correct it if you want to test it):       declare var varchar(5)      set var = '0'      select  2 / var      where var <> 0 or ISNUMERIC(var) = 1  .  I want the condition to exit because var IS equal to 0, but it goes ahead to check if it is numeric, which it is, and therefore the statement returns an error.
– tember
                Jun 22, 2015 at 16:43
                this one helped in my case where I had a different compare operator for each value of the type.
– Alex
                Oct 21, 2016 at 16:43
                better still  DECLARE @locationType NVARCHAR(50) = 'youchoose'  IF @locationType = 'location' BEGIN 	SELECT column1, column2 	FROM viewWhatever 	WHERE 		(account_location = @locationID) END   IF @locationType = 'area' BEGIN 	SELECT column1, column2 	FROM viewWhatever 	WHERE 		(xxx_location_area = @locationID) END   IF @locationType = 'division' BEGIN 	SELECT column1, column2 	FROM viewWhatever 	WHERE 		(xxx_location_division = @locationID) END
– Lukek
                Jan 27, 2017 at 14:47
    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
                Well, I would have written that as SELECT    column1,     column2 FROM    viewWhatever WHERE     (@locationType = 'location' AND account_location = @locationID)  OR (@locationType = 'area' AND xxx_location_area = @locationID)  OR (@locationType = 'division' AND xxx_location_division = @locationID)
– Jan de Vos
                Jan 12, 2010 at 10:42
                this is a great peace of example, how about the query execution plan with the best Answered ( personally I prefer this method code is clear and clean )
– PEO
                Aug 16, 2014 at 0:42
                really great if you want use different where clause with different type like int on 1st clause and nvarchar on the 2nd. with Bob Probst solution is does not work. thanks
– Julian50
                Jan 24, 2015 at 8:36
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
WHERE
    Account_Location = (
        CASE LocationType
          WHEN 'location' THEN @locationID
          ELSE Account_Location
    Account_Location_Area = (
        CASE LocationType
          WHEN 'area' THEN @locationID
          ELSE Account_Location_Area
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.
                I don't think this is a flawed table structure. The table was set up this way so that it was self references to have an infinite amount of parent/child relations. Believe me, it was on purpose. I don't think I want to change my table structure to just use a switch statement. its nto that important
– Miles
                Oct 15, 2008 at 22:09
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
so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.
OR operator can be alternative of case when in where condition
ALTER PROCEDURE [dbo].[RPT_340bClinicDrugInventorySummary]
    -- Add the parameters for the stored procedure here
     @ClinicId BIGINT = 0,
     @selecttype int,
     @selectedValue varchar (50)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
    drugstock_drugname.n_cur_bal,drugname.cdrugname,clinic.cclinicname
FROM drugstock_drugname
INNER JOIN drugname ON drugstock_drugname.drugnameid_FK = drugname.drugnameid_PK
INNER JOIN drugstock_drugndc ON drugname.drugnameid_PK = drugstock_drugndc.drugnameid_FK
INNER JOIN drugndc ON drugstock_drugndc.drugndcid_FK = drugndc.drugid_PK
LEFT JOIN clinic ON drugstock_drugname.clinicid_FK = clinic.clinicid_PK
WHERE   (@ClinicId = 0 AND 1 = 1)
    OR  (@ClinicId != 0 AND drugstock_drugname.clinicid_FK = @ClinicId)
    -- Alternative Case When You can use OR
    AND ((@selecttype = 1 AND 1 = 1)
    OR  (@selecttype = 2 AND drugname.drugnameid_PK = @selectedValue)
    OR  (@selecttype = 3 AND drugndc.drugid_PK = @selectedValue)
    OR  (@selecttype = 4 AND drugname.cdrugclass = 'C2')
    OR  (@selecttype = 5 AND LEFT(drugname.cdrugclass, 1) = 'C'))
ORDER BY clinic.cclinicname, drugname.cdrugname
    CASE @smartLocationType
        WHEN 'store' THEN account_location
        WHEN 'area' THEN xxx_location_area 
        WHEN 'division' THEN xxx_location_division 
        WHEN 'company' THEN xxx_location_company 
    END  = @smartLocation
                To whoever reads this in the future: it's the same as the accepted answer from @bob-prost above: stackoverflow.com/a/206500/264786
– ArturoTena
                Jun 19, 2015 at 20:17
                Downvoted because I don't understand how this could choose between the given strings (i.e. 'location', 'area', 'division')
– ArturoTena
                Jun 19, 2015 at 20:21
            SELECT * FROM tbl_City 
                WHERE 
                @StateID = CASE WHEN ISNULL(@StateId,0) = 0 THEN 0 ELSE StateId END ORDER BY CityName
    DROP TABLE Contacts
CREATE TABLE Contacts(ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO Contacts (ID, FirstName, LastName)
SELECT 1, 'Omid', 'Karami'
UNION ALL
SELECT 2, 'Alen', 'Fars'
UNION ALL
SELECT 3, 'Sharon', 'b'
UNION ALL
SELECT 4, 'Poja', 'Kar'
UNION ALL
SELECT 5, 'Ryan', 'Lasr'
DECLARE @FirstName VARCHAR(100)
SET @FirstName = 'Omid'
DECLARE @LastName VARCHAR(100)
SET @LastName = '' 
SELECT FirstName, LastName
FROM Contacts
WHERE  
    FirstName = CASE
    WHEN LEN(@FirstName) > 0 THEN  @FirstName 
    ELSE FirstName 
    LastName = CASE
    WHEN LEN(@LastName) > 0 THEN  @LastName 
    ELSE LastName
In general you can manage case of different where conditions in this way
SELECT *
FROM viewWhatever
WHERE 1=(CASE <case column or variable>
             WHEN '<value1>' THEN IIF(<where condition 1>,1,0)
             WHEN '<value2>' THEN IIF(<where condition 2>,1,0)
             ELSE IIF(<else condition>,1,0)
  WHEN website_id = 1 THEN 'TechOnTheNet.com'
  WHEN website_id = 2 THEN 'CheckYourMath.com'
  ELSE 'BigActivities.com'
FROM contacts;
                Downvoted because this answser is not related to the question. The question was how to use CASE on the WHERE clause, not how to use CASE on a SELECTed column.
– ArturoTena
                Jun 19, 2015 at 20:29
CREATE TABLE PER_CAL ( CAL_YEAR INT, CAL_PER INT )
INSERT INTO PER_CAL( CAL_YEAR, CAL_PER ) VALUES ( 20,1 ), ( 20,2 ), ( 20,3 ), ( 20,4 ), ( 20,5 ), ( 20,6 ), ( 20,7 ), ( 20,8 ), ( 20,9 ), ( 20,10 ), ( 20,11 ), ( 20,12 ), 
( 99,1 ), ( 99,2 ), ( 99,3 ), ( 99,4 ), ( 99,5 ), ( 99,6 ), ( 99,7 ), ( 99,8 ), ( 99,9 ), ( 99,10 ), ( 99,11 ), ( 99,12 )
The 4 digit century is determined by the rule, if the year is 50 or more, the century is 1900, otherwise 2000.
Given two 6 digit periods that mark the start and end period, like a quarter, return the rows that fall in that range.
-- 1st quarter of 2020
SELECT * FROM PER_CAL WHERE (( CASE WHEN CAL_YEAR > 50 THEN 1900 ELSE 2000 END + CAL_YEAR ) * 100 + CAL_PER ) BETWEEN 202001 AND 202003
-- 4th quarter of 1999
SELECT * FROM PER_CAL WHERE (( CASE WHEN CAL_YEAR > 50 THEN 1900 ELSE 2000 END + CAL_YEAR ) * 100 + CAL_PER ) BETWEEN 199910 AND 199912
Try this query. Its very easy to understand:
CREATE TABLE PersonsDetail(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
INSERT INTO PersonsDetail VALUES(N'Gourav', N'Bhatia', 2),
              (N'Ramesh', N'Kumar', 1),
              (N'Ram', N'Lal', 2),
              (N'Sunil', N'Kumar', 3),
              (N'Sunny', N'Sehgal', 1),
              (N'Malkeet', N'Shaoul', 3),
              (N'Jassy', N'Sohal', 2);
SELECT FirstName, LastName, Gender =
    CASE GenderID
    WHEN 1 THEN 'Male'
    WHEN 2 THEN 'Female'
    ELSE 'Unknown'
FROM PersonsDetail
                To whoever reads this answer: it's the same as the accepted answer from @bob-prost above: stackoverflow.com/a/206500/264786 Downvoted for this reason.
– ArturoTena
                Jun 19, 2015 at 20:27
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.