CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedureswhere-clause
Andrew Mcmellan
Andrew Mcmellan

Reputation: 31

How to select records where a column is equal to a parameter that can be null in SQL Server

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

Answers (3)

GMB
GMB

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

Gordon Linoff
Gordon Linoff

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

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use Boolean logic :

where (@client is null and clientid is null) or clientid = @client

Upvotes: 2

Related Questions