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

Loading

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
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
WP Plugin "User Specific Content" review OneDrive for Business 文件到 Google Drive 文件 Python - Crawling images from json file Handling long-press event Undo product custom field value in Ultimate Product Catalog Escape JSON Special Characters using PHP
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