Sql Server 2014 Null Value

Asked

Viewed 88 times

8

I am beginner in Sql server, I am in doubt regarding the following code:

declare @var nvarchar(50) = null

declare @bool bit

if (@var = null)
    set @bool = 1
else
    set @bool = 0

print @bool

The variable @bool returns as 0, even if I declare the variable @var null.

Wouldn’t it be to return as 1 ?

sql sql-server

  • See: http://answall.com/questions/87514/null-%C3%A9-equals-a-n

2 answers

10

This is one of the most particular subtleties of working with databases.

This:

if (@var = null) -- Preste atenção aqui
    set @bool = 1
else
    set @bool = 0

It’s different from this:

if (@var is null) -- Preste atenção aqui
    set @bool = 1
else
    set @bool = 0

The comparison = null in databases at all times will be false. This is because null is the absence of value, then it is not comparable, unlike checking whether the value is null, what makes the return be 1.

1

You will not be able to compare a field value null with the operator =, for that exists the IS NULL that is to say.

if (@var is null) 
    set @bool = 1
else
    set @bool = 0

There is one thing also that has to look at its @var variable can still have an Empty value where you could check as follows.

IF (@var IS NOT NULL) OR (LEN(@var) > 0)
    set @bool = 1
else
    set @bool = 0

See more details.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.