How to use CASE/IF statement in WHERE clause?

Question

I have 2 variables declared i.e @empCode and @centerCode. A center contains multiple employees. I have another variable that is @IsEmployeeBased Now if @IsEmployeeBased = 1 then it will use @empCode else it will use @centerCode. My query is something like this.

    DECLARE @IsEmployeeBased INT = 1;
    DECLARE @empCode INT = 1;
    DECLARE @centerCode INT = 3;
    SELECT * FROM Employee
    WHERE Department = 'Information Tech'
    AND 
    CASE WHEN @IsEmployeeBased = 1 THEN ID = @empCode ELSE CenterCode = @centerCode

This is returning error, which I suppose is CASE is not allowed in where clause. Is there any way to get the desired query working ?

Here is sample data:

CREATE TABLE Employee
(
ID Int,
EmployeeName VARCHAR(25),
Department VARCHAR(25),
CenterCode INT 
)

INSERT INTO Employee VALUES (1, 'Asim', 'Information Tech', 4)
INSERT INTO Employee VALUES (2, 'Ali', 'Information Tech', 2)
INSERT INTO Employee VALUES (3, 'Isaac', 'Information Tech', 3)
INSERT INTO Employee VALUES (4, 'Swagger', 'Information Tech', 4)
INSERT INTO Employee VALUES (5, 'Nadine', 'Information Tech', 2)
INSERT INTO Employee VALUES (6, 'Julie', 'Information Tech', 4)
INSERT INTO Employee VALUES (7, 'Meachum', 'Information Tech', 3)
INSERT INTO Employee VALUES (8, 'Bob Lee', 'Information Tech', 4)

Possible duplicate of [SQL use CASE statement in WHERE IN clause](https://stackoverflow.com/questions/19271853/sql-use-case-statement-in-where-in-clause) — Dale K, Feb 21 '19 at 06:44

score 1 · Accepted Answer · answered Feb 21 '19 at 06:28

The predicate of a CASE expression (i.e. what follows THEN or ELSE) has to be a constant value, not another logical expression. You may rephrase your WHERE clause to not use a CASE expression:

WHERE
    Department = 'Information Tech' AND (
    (@IsEmployeeBased = 1 AND ID = @empCode) OR
    (@IsEmployeeBased <> 1 AND enterCode = @centerCode))

score 0 · Answer 2 · answered Feb 21 '19 at 07:05

You can also try by creating dynamic executable query as shown below.

DECLARE @IsEmployeeBased INT = 0;
DECLARE @empCode INT = 1;
DECLARE @centerCode INT = 3;

DECLARE @Query NVarchar(500)
SET @Query = N'SELECT * FROM Employee WHERE Department = ''Information Tech'''
DECLARE @Where Varchar(250) = ''
IF (@IsEmployeeBased = 1)
BEGIN
    SET @Where += 'AND ID = ' + Convert(char(5), @empCode) +''
END
ELSE IF(@IsEmployeeBased <> 1)
BEGIN
    SET @Where += 'AND CenterCode = ' + Convert(char(5), @centerCode) +''
END
SET @Query = @Query + @Where 

--Select @Query
EXECUTE sp_executesql @Query

Please also have a look into this thread.

How to use CASE/IF statement in WHERE clause?

2 Answers2