IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. Database
  4. Null values concerns in SQL Server

Null values concerns in SQL Server

2019-01-02 1639 Views 1 Like 0 Comments

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, '')

 3,105 total views,  4 views today

error
fb-share-icon
Tweet
fb-share-icon
Richard Zhang
Author: Richard Zhang

Tags: Blog Concerns Considerations IsNull Null
Last updated:2019-01-02

Richard Zhang

This person is lazy and left nothing

Like
< Previous
Next >

Comments

Cancel reply
Chinese (Simplified) Chinese (Simplified) Chinese (Traditional) Chinese (Traditional) English English French French German German Japanese Japanese Korean Korean Russian Russian
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Post tweets with Power Automate CrellySlider - make full width slider Sending mail from SSIS Send web report by email from report page Converting asp.net website to wordpress How to auto-move to the bottom of a block
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com