Jonathan Porter Jonathan Porter - 1 month ago 4
SQL Question

How to assign value using Case statement in where clause

The logic I'm trying to implement is, if

greatPlainsUserId
is not null then use it, otherwise use
userName
but I'm getting syntax errors.

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

Answer

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.

Comments