Select query where query values can be null

Question

My table "mytable" in MS SQL Server looks like:

|Id|ka|kb|kc  |kd
|1 | 1| 1|NULL|NULL
|2 | 1| 1|1   |NULL
|3 | 1| 1|1   |1

where Id, ka kb can not be null but kc and kd can be null.

Can I select from mytable with variables that also can be null ?

int? varka = 1, varkb = 1, varkc = null, varkd = null;

select Id from mytable where ka = varka and kb = varkb and kc = varkc and kd = varkd

My desired Id from mytable is "1", only "1" but I am getting a NULL in C#, or no result in T-SQL.

No, if you are writing T-SQL, then almost always you are going to be using ANSI-null behavior, which means null never equals anything: not even null - you need to use is null to test for nulls. It isn't clear how you are adding the parameters, though, so I can't be clear what you're doing here. — Marc Gravell
– Marc Gravell, Commented Jul 1, 2020 at 20:15

GMB · Accepted Answer · 2020-07-01 20:14:25Z

1

You seem to be looking for a null-safe equality. In standard SQL, we have operator IS DISTINCT FROM, but SQL Server does not supports it (and provides not equivalent operator).

So we are left with boolean logic:

select id
from mytable 
where 
    ka = varka 
    and kb = varkb 
    and ( (kc is null and varkc is null) or kc = varkc )
    and ( (kd is null and varkd is null) or kd = varkd )

answered Jul 1, 2020 at 20:14

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select query where query values can be null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related