An Is Null Gotcha
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/anisnullgot
-
Experience suggests...if user says data is being truncated ,first check the size of any fields (or intermediate fields) used to store that data.
Regards
HMC
-
That's not a gotcha, that's a "Man you wasted valuable development hours to figure that out!"
-
Title should have read IsNull and not Is Null. However interesting effect as you found out.
Example.
declare @x varchar(5)
select isnull(@x,'1234567890')
From BOL
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Keep in mind type is not varchar but the varchar(5). Many folks forget that.
-
Thank you very much for your leg work it is something difficult to spot and definitely will go into my bag of "check before release"
* Noel -
Nice work writing that up. I can see that saving me a headache one day.
-
If you look on the Microsoft SQL Server site you will see that there is a books on-line update dated January 2004.
You can tell if you need the update by looking at the documentation for the sp_addlogin stored procedure.
If example E for copying passwords says
CONVERT(VARBINARY(32),[Password])
instead of
CONVERT(VARBINARY(256),[Password])
then you need to update BOL.
-
excellent article . . . both for the "gotcha" and as a reminder to do your homework . . . lookup "Using Data Types" in bol. type AND length are specified as two of the four attributes of a data type assignment.
-
I enjoyed this article, good description of problem solving exercise.
But I don't think the title or any focus on isnull() is right.
Assume some other function was used instead of isnull--maybe upper() for the sake of argument. Still would have had same problem with truncation. The problem was with variable declaration & fact that escaping process expanded size of string.
I've seen more problems related to isnull() or lack of it than almost any other single area, but this is not one of them.
-
Isn't Coalesce preferred over IsNull, being ansi standard and all? You wouldn't have had this problem anyway...
-
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.
-
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 -
They are talking about the function ISNULL not the operand "is null".
-
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 -
That's been said too (buried somewhere in the first posts (antares))
.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply