CODEWITHSUNDEEP
sqlsql-servert-sql
thecodeexplorer
thecodeexplorer

Reputation: 373

SQL Server - Using CASE statement

I have a SELECT statement with a WHERE clause that I want to dynamically change depending if a parameter is supplied or not.

I can't seem to understand how to use CASE statement in a WHERE clause but this is how I want it to look like using an IF statement.

DECLARE @Gender NVARCHAR(100) = NULL --this is an INPUT parameter and may or may not be NULL
DECLARE @Status NVARCHAR(100) = NULL --this is an INPUT parameter and may or may not be NULL

    SELECT Name
    FROM Person
    WHERE 
        -- first WHERE clause
        IF @Gender IS NULL
           BEGIN
                Gender IS NULL
           END
        ELSE
           BEGIN
                Gender = @Gender
           END
    AND
        -- second WHERE clause
        IF @Status IS NULL
           BEGIN
                Status IS NULL
           END
        ELSE
           BEGIN
                Status LIKE '%' + @Status + '%'
           END

Is it possible to transform this code into a CASE statement?

Upvotes: 1

Views: 84

Answers (3)

Michael G
Michael G

Reputation: 6745

DECLARE @Gender NVARCHAR(100) = NULL --this is an INPUT parameter and may or may not be NULL
DECLARE @Status NVARCHAR(100) = NULL --this is an INPUT parameter and may or may not be NULL

SELECT Name
FROM Person
WHERE CASE WHEN @Gender IS NULL THEN 1
           WHEN @Gender = ISNULL(Gender, '') THEN 1
           ELSE 0
      END = 1
  AND CASE WHEN @Status IS NULL THEN 1
           WHEN ISNULL(Status, '') LIKE '%' + @Status + '%' THEN 1
           ELSE 0
      END = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want:

select p.name
from person p
where ( (@gender is null and gender is null) or gender = @gender) and
      ( (@status is null and status is null) or status = @status);

Note that this does "null-matching". Often, people want to use NULL to select all records, not just the NULL ones. If that is what you intend, then:

select p.name
from person p
where ( @gender is null or gender = @gender) and
      ( @status is null  or status = @status);

In either situation, case is not needed in the where. As a general rule, don't use case in where -- unless you really need it to control the order of evaluation of expressions.

Upvotes: 2

Gauravsa
Gauravsa

Reputation: 6524

You can do this:

SELECT Name
FROM Person
WHERE Gender = COALESCE(@gender, Gender)
AND (@Status is null or Status like '%' + @status + '%')

Upvotes: 0

Related Questions