CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vikas Vaidya
Vikas Vaidya

Reputation: 368

SQL where clause CASE switch with multiple THEN

There are a few EmployeeType in Employee table but categorized as 'Internal' or 'External'. External type is further categorized as 'Contract' or 'Outsource'. I am writing a proc in which I receive a param as @EmployeeType. I am expecting 'Internal' or 'External' or 'ALL' in parameter value.

Here is my query

SELECT * FROM EMPLOYEE WHERE EmploymentType = CASE WHEN @EmployeeType = 'Internal' WHEN @EmployeeType = 'External' THEN 'Contract' OR 'Outsource' ELSE EmployeeType END

Available EmployeeTypes in table -- Internal, External, Contract, Outsource

After searching a lot on web, I couldn't find a proper syntax to get the desired output. Can anyone please help.

I know its a bad design but its a legacy system and nothing much I can do with it.

Upvotes: 0

Views: 229

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

Also you can try this:

    DECLARE @EmployeeType VARCHAR(100)='Internal'
    ;WITH EMPLOYEE(ID,EmploymentType)AS
    (
      SELECT 1,'Internal' UNION
      SELECT 2,'Contract' UNION
      SELECT 3,'Outsource'
    )
    SELECT * FROM EMPLOYEE 
    WHERE EmploymentType IN (CASE @EmployeeType WHEN 'External' THEN 'Contract' WHEN 'Internal' THEN @EmployeeType ELSE EmploymentType END
                           ,CASE WHEN @EmployeeType = 'External' THEN 'Outsource' ELSE NULL END)

--Internal----

ID          EmploymentType
----------- --------------
1           Internal

------External----------

ID          EmploymentType
----------- --------------
2           Contract
3           Outsource

-----ALL ----

ID          EmploymentType
----------- --------------
1           Internal
2           Contract
3           Outsource

Upvotes: 0

MatBailie
MatBailie

Reputation: 86775

I wouldn't even use a CASE statement...

WHERE
       (@EmployeeType = 'All')
    OR (@EmployeeType = 'Internal' AND EmploymentType = 'Internal')
    OR (@EmployeeType = 'External' AND EmploymentType IN ('External', 'Outsource', 'Contract') )

EDIT:

That said, no matter how you implement this, you may not get a very good execution plan. It's in depth, but the reasoning is here: http://www.sommarskog.se/dyn-search.html

TL;DR - It's going to be more performant to use Multiple different queries inside IF blocks or Dynamic SQL.

EDIT:

If you want to do this with a CASE statement, or it's an academic exercise in how this type of thing should be phrased...

In SQL Server, the CASE statement can only return a scalar value. It can't return a list, or even a Boolean result. This forces you to something with nested conditions.

WHERE
  CASE @EmployeeType
    WHEN 'All'
      THEN 1
    WHEN 'Internal'
      THEN CASE WHEN EmploymentType = 'Internal' THEN 1 END
    WHEN 'External'
      THEN CASE WHEN EmploymentType IN ('External', 'Outsource', 'Contract') THEN 1 END
  END
  =
  1

Which isn't very pretty.

Another option would be to use a sub-query that can return a set of values to compare against.

WHERE
    EmploymentType IN (
        (
            SELECT 'Internal'  AS type WHERE @EmployeeType IN ('All', 'Internal')
            UNION ALL
            SELECT 'External'  AS type WHERE @EmployeeType IN ('All', 'External')
            UNION ALL
            SELECT 'Contract'  AS type WHERE @EmployeeType IN ('All', 'External')
            UNION ALL
            SELECT 'Outsource' AS type WHERE @EmployeeType IN ('All', 'External')
        )
    )

Upvotes: 3

hopeIsTheonlyWeapon
hopeIsTheonlyWeapon

Reputation: 567

Do you want to do anything for 'Internal' and 'All' if not then just a If statement should suffice as below IF (@EmployeeType = 'External') BEGIN //Write your logic here.

END

Upvotes: 0

Related Questions