asked on

How do I Nest cases or If in CASE MS SQL

I have the following condition in my query
WHERE      StartDate>=@UserDate
      AND
      CASE @Type
            WHEN 1 THEN      CASE WHEN yearCol = @year THEN 1 ELSE 0 END
            WHEN 2 THEN       CASE WHEN color<>@Color THEN 1 ELSE 0 END
            END = 1
In some case I have @Tpype=2 and @color=null
What I would like to do in the second statement (when 2 then..) check to see if @color is null where I will do something like this
CASE @color
When null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
When not Null Then CASE color <> @Color THEN 1 ELSE 0 END
END=1
The above syntax is incorrect what's the right syntax please note that what I am doing in those cases is executing a condition based on the case
Thank you

appari

try this

WHERE StartDate>=@UserDate
AND
CASE @Type
WHEN 1 THEN CASE WHEN yearCol = @year THEN 1 ELSE 0 END
WHEN 2 THEN
      CASE
       When @color is null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
       When @color is not Null Then CASE color <> @Color THEN 1 ELSE 0 END
      end
END=1

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xav056

ASKER

incorrect syntax wiggly lines under the <> anf then
e.GDID<>@GDID (incorrect syntax bear <> and incorrect syntax near then

Thank you

appari

>>incorrect syntax wiggly lines under the <> anf then
>>e.GDID<>@GDID (incorrect syntax bear <> and incorrect syntax near then
seems the error is now from a different place. post your complete query.

xav056

ASKER

appari
nothing before just
Select *
from VehInfo
WHERE StartDate>=@UserDate
AND
CASE @Type
WHEN 1 THEN CASE WHEN yearCol = @year THEN 1 ELSE 0 END
WHEN 2 THEN
CASE
When @color is null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
When @color is not Null Then CASE color <> @Color THEN 1 ELSE 0 END
end
END=1

SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xav056

ASKER

Thank you both

cyberkiwi

A properly nested CASE statement - 3 levels, can become really confusing

CASE
WHEN @Type=1 THEN
CASE WHEN yearCol = @year THEN 1 ELSE 0 END
WHEN @Type=2 THEN
CASE
WHEN @color IS NOT NULL THEN
CASE WHEN color<>@Color THEN 1 ELSE 0 END
ELSE -- the complement is clearly null
CASE WHEN color<>@NewColor THEN 1 ELSE 0 END
END
END = 1

cyberkiwi

Sorry was distracted, here's part 3 of 3.
For your case, you only want @newcolor when @color is null, you can use a better technique instead of CASE

CASE
WHEN @Type=1 THEN
CASE WHEN yearCol = @year THEN 1 ELSE 0 END
WHEN @Type=2 THEN
CASE WHEN color<>Coalesce(@Color,@NewColor) THEN 1 ELSE 0 END
END = 1