CODEWITHSUNDEEP
sql-serverstored-procedures
Srinivasan
Srinivasan

Reputation: 12050

SQL Server if condition NULL checking

I have written SQL Server procedure like below,

if @name_belongs_to != 'John'
begin
  --doing some operation
end

If the name is not 'John', it is working fine. However if it is NULL, it is not executing if part.

How to handle this?

Upvotes: 2

Views: 744

Answers (5)

Srinivasan
Srinivasan

Reputation: 12050

I have used below statement to resolve my issue,

if isnull(@name_belongs_to,'') != 'John'

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

By default any comparison to null returns false

null = null is false
null = 'value' is false

so you need to add 

OR @name_belongs_to IS NULL

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28940

just use is not null

if @name_belongs_to != 'John' or @name_belongs_to is not  null
begin
  --doing some operation
end

Upvotes: 0

Alex
Alex

Reputation: 5167

This is also valid:

if @name_belongs_to != 'John' OR @name_belongs_to IS NULL
begin
  --doing some operation
end

This MSDN article explains how Three-Valued Logic works.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

One option would be to use COALESECE() on the name:

if coalesce(@name_belongs_to, '') != 'John'
begin
  --doing some operation
end

You cannot compare NULL values in SQL Server (and most other RDBMS) using the equals operator. Instead, you need to use IS NULL or IS NOT NULL. Using COALESCE() here is a trick which will convert a NULL value to a string for comparison with !=.

Upvotes: 4

Related Questions