How to assign value using Case statement in where clause

Question

The logic I'm trying to implement is, if greatPlainsUserId is not null then use it, otherwise use userNamebut I'm getting syntax errors.

    ...
    WHERE (CASE
    WHEN @greatPlainsUserId IS NULL 
    THEN BUYERID = @userName 
    ELSE BUYERID = @greatPlainsUserId
    END)
    AND...

It is perhaps semantics, but it is a case **expression**, not a case **statement**, it is an expression because it returns a value. Even Microsoft are guilty of this misnomer, but I think calling it a statement leads to attempts to use it like a control flow as you have done. For further reading see - [Dirty Secrets of the CASE Expression](https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression) — GarethD, Oct 19 '16 at 16:26
basically you would just use case like `WHERE BUYERID = (CASE WHEN @greatPlainsUserId IS NULL THEN @userName ELSE @greatPlainsUserId END)`.. which is basically what `WHERE BUYERID = ISNULL(@greatPlainsUserId, @userName)` is doing — JamieD77, Oct 19 '16 at 17:26
A `case` expression returns a value, but `boolean` is not supported. An example of using `case` in a `join` condition is [here](http://stackoverflow.com/a/10260297/92546). (There is a [boolean](https://msdn.microsoft.com/en-us/library/ms188074.aspx#Anchor_0) data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one: "Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.") — HABO, Oct 19 '16 at 20:28

score 4 · Accepted Answer · answered Oct 19 '16 at 16:17

Remove the CASE. Just use simple logic:

WHERE ((@greatPlainsUserId IS NULL AND BUYERID = @userName) OR
       (BUYERID = @greatPlainsUserId)
      )

The second condition will only work if @greatPlainsUserId IS NOT NULL, so that condition is redundant.

Alternatively:

WHERE BUYERID = COALESCE(@greatPlainsUserId, @userName)

This is probably the simplest logic.

Jeff.Clark · Answer 2 · 2016-10-19T18:19:30.440

Just use ISNULL() It will take care of both situations. Run the below test code to see. If IssCost is not null, it will use IssCost. If it is null, it will say "Boooo!"

Sorry for my lazy example data, I had the data already made and didn't feel like modifying it much :)

CREATE TABLE Payroll (
    EmployeeID int NULL
   ,PlanCode varchar(10) NULL
   ,IssCost varchar(10) NULL
)
GO

INSERT INTO Payroll (EmployeeID, PlanCode, IssCost)
VALUES (1, 'Medical', '200')
      ,(1, 'Dental', '250')
      ,(1, 'Vision', '300')
      ,(2, 'Medical', '100')
      ,(2, 'Dental', '150')
      ,(2, 'Vision', NULL)


SELECT EmployeeID
      ,PlanCode
      ,ISNULL( IssCost, 'Boooo!')
FROM Payroll

How to assign value using Case statement in where clause

2 Answers2