CODEWITHSUNDEEP
sqlsql-server
Springray
Springray

Reputation: 99

CASE statement in WHERE clause in SQL (SQL Server)

I tried to use CASE statement in WHERE clause, but it didn't work. Any help is really appreciated, thank you!!

SELECT
...
FROM ...
WHERE
  CASE
    WHEN @LastName <> '' AND @CardNo <> '' AND @StoreNo <> '' THEN
      ((m.ApplicantLastName LIKE @LastName AND m.StoreNo = @StoreNo) OR
       (m.CardNo LIKE @CardNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> ''))
    WHEN @LastName <> '' AND @CardNo <> '' THEN
       (m.ApplicantLastName LIKE @LastName OR (m.CardNo LIKE @CardNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> ''))
    WHEN @LastName <> '' AND @StoreNo <> '' THEN
       (m.ApplicantLastName LIKE @LastName AND m.StoreNo = @StoreNo)
    WHEN @LastName <> '' THEN
        m.ApplicantLastName LIKE @LastName
    WHEN @CardNo <> '' THEN
       (m.CardNo LIKE @CardNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> '')
  END

Upvotes: 0

Views: 129

Answers (4)

Springray
Springray

Reputation: 99

I finally got it working with the following:

SELECT
...
FROM ...
WHERE
  CASE
    WHEN @LastName <> '' AND @BimartNo <> '' AND @StoreNo <> '' AND
      ((m.ApplicantLastName LIKE @LastName AND m.StoreNo = @StoreNo) OR
       (m.BimartNo LIKE @BimartNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> '')) THEN 1
    WHEN @LastName <> '' AND @BimartNo <> '' AND @StoreNo IS NULL AND
       (m.ApplicantLastName LIKE @LastName OR (m.BimartNo LIKE @BimartNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> '')) THEN 1
    WHEN @LastName <> '' AND @StoreNo <> '' AND @BimartNo IS NULL AND
       (m.ApplicantLastName LIKE @LastName AND m.StoreNo = @StoreNo) THEN 1
    WHEN @LastName <> '' AND @BimartNo IS NULL AND @StoreNo IS NULL AND
        m.ApplicantLastName LIKE @LastName THEN 1
    WHEN @BimartNo <> '' AND @LastName IS NULL AND @StoreNo IS NULL AND
       (m.BimartNo LIKE @BimartNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> '') THEN 1
    ELSE 0
  END = 1

What I learned, if we want to do:

WHERE
  IF A THEN X
  ELSE IF B THEN Y

In SQL, it would be:

WHERE
  CASE
    WHEN A AND X THEN 1
    WHEN B AND Y THEN 1
    ELSE 0
  END = 1

Denis/lrb/Gordon: Thanks for all you input!

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15175

The parameters can be used in the where clause as it there were fields to combine into a filter.

WHERE
    (@CardNo='' OR m.CardNo LIKE @CardNo)       
    AND
    (@LastName='' OR m.ApplicantLastName LIKE @LastName)
    AND
    (@StoreNo='' OR  m.StoreNo = @StoreNo)
    AND
    (@CardNo='' OR LTRIM(RTRIM(m.ApplicantLastName)) <> '')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can rewrite you logic without case by doing something like this:

WHERE ((@LastName = '' AND LTRIM(RTRIM(m.ApplicantLastName)) <> '') OR 
       (m.ApplicantLastName LIKE @LastName)
      ) AND
      (@CardNo = '' OR m.CardNo LIKE @CardNo) AND
      (@StoreNo = '' OR m.StoreNo = @StoreNo)

Note that this logic is often written assuming NULL instead of an empty string:

WHERE (@LastName IS NULL OR m.ApplicantLastName LIKE @LastName) AND
      (@CardNo IS NULL OR m.CardNo LIKE @CardNo) AND
      (@StoreNo IS NULL OR m.StoreNo = @StoreNo)

And, if you have indexes on these columns, then you might want to consider dynamic SQL so the final query can make use of the indexes.

Upvotes: 0

Denis Reznik
Denis Reznik

Reputation: 964

This is a wrong usage of CASE:

(m.CardNo LIKE @CardNo AND LTRIM(RTRIM(m.ApplicantLastName)) <> '')

You can't use it to generate predicates on a flight. You can use CASE only to generate a value, which you can compare with something in WHERE clause.

Upvotes: 4

Related Questions