Null values concerns in SQL Server

Null value concerns and considerations in SQL Server.

In SQL Server scripting, we often use the IsNull function to avoid null value issue. Perhaps occasionally you may see the script like here below that does not work well?

select @myIndex = MAX(ISNULL([Index], 0)) + 1  from myTable

It seems that it is smart to get the new index value with one statement and make the script briefly. However, it does not work well, and the null value will not always avoid.  For instance, let us see below:

We create a temp table here,

Create Table #myTable(

    FName nvarchar(50),
    LName nvarchar(50),
    Email nvarchar(300)

)

Let us insert one record like this:

insert into #myTable (FName,LName) values('Richard', 'Zhang')

We get the email value :

declare @myEmail nvarchar(300)
select @myEmail = ISNULL(email, '') from #myTable where FName = 'Richard'
select @myEmail

This case we avoid the null value. It works. Good luck. Then let us the following statement, the null value comes up even the isnull function is used:

declare @myEmail2 nvarchar(300)
select @myEmail2 = ISNULL(email, '') from #myTable where FName = 'Albert'
select @myEmail2

What happens? Why?

Let’s say if the query return row(s) exists, and the IsNull function works fine if the query result is nothing, I mean no record returned, then you IsNull function won’t work fine for you.  What should we do for this issue? Null value process in the second statement instead of a combination of your SQL command. Just simply separate your statements, it won’t waste you too much time. See here below. Hopefully, it helps.

select @myEmail = ISNULL(email, '') from #myTable where FName = 'Richard'
--==>Change as:
select @myEmail = email from #myTable where FName = 'Richard'
select @myEmail = ISNULL(@myEmail, '')

select @myEmail2 = ISNULL(email, '') from #myTable where FName = 'Albert'
--==>Change as
select @myEmail2 = email from #myTable where FName = 'Albert'
select @myEmail2 = ISNULL(@myEmail2, '')

Author: Richard Zhang