Case Statement in where

Question

Case Statement in where

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 15, 2021 at 1:54 pm

#3963902

Hi
Is it possible to do like this
@ItemGroup is a parameter
Select Itemcode,Itemname,ItemGroup
from tbl1
Case when @ItemGroup is null then
where itemgroup is null
else
where itemgroup is not null
end
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe like this

if @ItemGroup is null
    Select Itemcode,Itemname,ItemGroup
    from tbl1
    where itemgroup is null;
else
    Select Itemcode,Itemname,ItemGroup
    from tbl1
    where itemgroup is not null;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 2

jagjitsingh

Ten Centuries

Points: 1059

December 15, 2021 at 2:19 pm

#3963909

Hi Steve

Can't we use with where clause only

Thanks

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

This, maybe

Select Itemcode,Itemname,ItemGroup
    from tbl1
    where (itemgroup is null and @ItemGroup is null) or (itemgroup is not null and @ItemGroup is not null);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

My understanding, it won't work QUITE like that. BUT what you could do is something like:

SELECT Itemcode,Itemname,ItemGroup
FROM tbl1
WHERE (itemgroup IS NULL and @ItemGroup IS NULL) 
  OR (itemgroup IS NOT NULL and @ItemGroup IS NOT NULL)

Same basic logic without the CASE statement. You can prove this with a truth table pretty easily.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.