Reputation: 31
I have this table
create table [test]
(
id int identity(1,1) primary key,
name nvarchar(50),
clientid int null -- Can be null
)
I have this stored procedure:
create procedure sp_GetValue
(@client int = null)
begin
select *
from test
where clientid = @client
end
Problem is when client is equal to null it returns nothing even though I have records with null clientid
Upvotes: 2
Views: 787
Reputation: 222722
You want null-safe comparison. Unlike some other databases, SQL Server does not have a specific operator for this, so you are left with some logic to describe the possible cases:
select *
from test
where (clientid is null and @client is null) or clientid = @client
Upvotes: 2
Reputation: 1271231
You need to use or
:
select *
from test
where clientid = @client or (clientid is null and @client is null);
Unfortunately, SQL Server does not have a NULL
safe equality operator (the standard is is not distinct from
).
Also, the OR
might have an impact on index usage. You can include option (recompile)
which should fix that problem.
Upvotes: 2
Reputation: 50173
Use Boolean logic :
where (@client is null and clientid is null) or clientid = @client
Upvotes: 2