Case Statement Multiple Then values

Question

Not sure if this makes sense, I am trying to edit an Existing sproc to add a variable to the "Where" clause to either Return all of the Value, None of the Value, or every record regardless of the Value.

I am using a Case When/ Then statement in the Where clause.

  WHERE 
      CASE (@work)
  WHEN 'Y' THEN part.corpid = ( 'Work EMP' , 'Work EMP 10' , 'Work FAM' )
  WHEN 'N' THEN part.corpid != ( 'Work EMP' , 'Work EMP 10' , 'Work FAM' )   
  ELSE part.corpid = *
  END

The setup is if its Y, then return only employee's and their families, if N then do no return any employee or their Family, and if NULL in the variable, then return everything.

I have no idea what I am screwing up here, I keep getting errors on the = in the Y Section.

Solution

You should be able to use the following WHERE clause:

WHERE 
(
  @work = 'Y'
  AND part.corpid IN ( 'Work EMP' , 'Work EMP 10' , 'Work FAM' )
)
OR
(
  @work = 'N'
  AND part.corpid NOT IN ( 'Work EMP' , 'Work EMP 10' , 'Work FAM' )
) 
OR
(
  @work is null 
  -- add other filters here if needed
  -- no need to include the part.corpid because all will be included
)