SQL Server if condition NULL checking

Question

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?

This is a symptom of three valued logic, read here for more information; simple-talk.com/sql/learn-sql-server/… — Rich Benner
– Rich Benner, Commented Jun 13, 2016 at 8:03

Tim Biegeleisen · Accepted Answer · 2016-06-13 07:58:56Z

4

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 !=.

answered Jun 13, 2016 at 7:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Alex · Accepted Answer · 2016-06-13 08:05:47Z

2

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.

edited Jun 13, 2016 at 8:05

answered Jun 13, 2016 at 7:59

Alex

5,2123 gold badges24 silver badges44 bronze badges

Comments

TheGameiswar · Accepted Answer · 2016-06-13 08:05:53Z

0

just use is not null

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

edited Jun 13, 2016 at 8:05

answered Jun 13, 2016 at 7:59

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Comments

paparazzo · Accepted Answer · 2016-06-13 08:07:48Z

0

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

answered Jun 13, 2016 at 8:07

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Comments

Srinivasan · Accepted Answer · 2016-06-13 09:26:26Z

0

I have used below statement to resolve my issue,

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

answered Jun 13, 2016 at 9:26

Srinivasan

12.1k30 gold badges70 silver badges94 bronze badges

Collectives™ on Stack Overflow

SQL Server if condition NULL checking

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related