CODEWITHSUNDEEP
t-sql
Tan
Tan

Reputation: 2178

TSQL case with or in it

My problem:

In my WHERE statement "Shock.Comment" is null-able. The result i want to achieve is if the parameter

@missingComments = 1

then i want to get all Shocks that has string empty or null. What i have TRIED:

WHERE Shock.Comment = CASE WHEN @missingComments = 1 THEN '' OR IS NULL END

How can i get all posts with comment = null or empty when

@missingComments = 1

Upvotes: 0

Views: 64

Answers (2)

bradlis7
bradlis7

Reputation: 3489

CASE is very picky, and can't evaluate to BIT, and you can't do v = A OR B, you have to do v = A OR v = B. This is the solution I came up with.

WHERE @missingComment = 0 OR (Shock.Comment = NULL OR Shock.Comment = '')

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56745

This is one way:

WHERE (  (@missingComments != 1)
      OR (COALESCE(Shock.Comment, '') = '' )

Note that any answer to this using a single static SQL query will not be able to use any indexes on Shock.Comment to improve performance.

Upvotes: 2

Related Questions