SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Is Null Gotcha


An Is Null Gotcha

Author
Message
Mike C
Mike C
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6547 Visits: 1172

You wouldn't have had this issue with COALESCE(), which is ANSI SQL standard anyway. Here's an example:

DECLARE @c CHAR(2)
SELECT COALESCE(@c, 'HELLO')
SELECT ISNULL(@c, 'HELLO')

The COALESCE() version returns what you expect. The ISNULL() version returns 'HE'. One more reason to use ANSI SQL-92 standard functionality in your code when presented with the choice.

See http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp, Rule #4.


dolphin
dolphin
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 1

Hey there...

So you are saying that if I have the following “validation” code at the beginning of my SP (after variable declaration and before INSERT)_, that I should be using COALESCE instead of IS NULL:

Note: @PatientHospitalID VARCHAR (10), @GenderID INT

--Excerpts from code

SELECT @PatientHospitalID = LTRIM(RTRIM(@PatientHospitalID))
IF @PatientHospitalID IS NULL
SELECT @PatientHospitalID = 0

SELECT @GenderID = LTRIM(RTRIM(@GenderID))
IF @GenderID IS NULL --needs to have a valid value!
SELECT @GenderID =3 --3 = UNKNOWN

Just wondering

Thanks.
Michelle/Dolphin.



"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68229 Visits: 9671
They are talking about the function ISNULL not the operand "is null".
dolphin
dolphin
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 1

Right... I think the article title mis-lead me (along with a couple of the posts)...

Title should be ISNULL () then!



"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68229 Visits: 9671
That's been said too (buried somewhere in the first posts (antares)) .
Dan Sydner
Dan Sydner
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 10
On the other if you were to use COALESCE you code could look like this

SELECT @PatientHospitalID = COALSESCE (LTRIM(RTRIM(@PatientHospitalID)), 0)

shorter and eaiser to read imho
dolphin
dolphin
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 1

Dan,

First of all...Thanks for actually replying to my question (instead of pointing out errors, duplication, etc in my posts/comments) like Remi did Your post was actually helpful and responded to my question, unlike the ones I'm referring to

Thanks for this code snippet, I will try it out. But in looking in BOL, I couldn't find any usage like you've shown as far as the "generic" syntax making sense... and that is why I wouldn't have thought to use COALESCE in this situation. Is this type of usage documented anywhere else that you know of and will it cause any differences OR function any different than the original code I had?

Thanks in advance for your responses!
Michelle/Dolphin.



"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb
Mike C
Mike C
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6547 Visits: 1172

Hi Michelle,

The COALESCE() function is documented here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_9dph.asp and here http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

COALESCE() performs a similar function to ISNULL(), but it is more flexible and it has been standardized. In addition to the code snippet posted previously, the second half of your code can be re-written to take advantage of COALESCE() as well:

SELECT @PatientHospitalID = COALESCE(LTRIM(RTRIM(@PatientHospitalID)), '0')

SELECT @GenderID = COALESCE(@GenderID, -3)

Note that we don't need the LTRIM(RTRIM()) around @GenderID since it's an Integer type.


ken-363325
ken-363325
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Curious why would you update a password - when the fact is the client never had the "ability" to update it anyway ? Why not just skip the update and leave it alone..??

But I guess the main point here is when you do something like this Data Type and Field Length should be looked at closely.

Also of note - I have seen in more than one industry operator error is the "PHRASE of the DAY" and 95% of the time programmer error is really the issue, whether it be poor UI design or poor program design/coding.
sean-653091
sean-653091
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 Visits: 68
Yes, COALESCE seems to me to be a safer option. Try this:

DECLARE @vcOne varchar(10)
DECLARE @vcTwo varchar(20)
SET @vcTwo = '01234567890123456789'
SELECT ISNULL(@vcOne,@vcTwo) -- will truncate to 10
SELECT COALESCE(@vcOne,@vcTwo) -- won't
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search