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


An Is Null Gotcha


An Is Null Gotcha

Author
Message
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7831 Visits: 3290
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/anisnullgot

LinkedIn Profile

Newbie on www.simple-talk.com
Hamish McCreight-154771
Hamish McCreight-154771
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
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
Scott Hall
Scott Hall
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 2
That's not a gotcha, that's a "Man you wasted valuable development hours to figure that out!"
Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11828 Visits: 780

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.





noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9864 Visits: 2048

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
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11917 Visits: 2730
Nice work writing that up. I can see that saving me a headache one day.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7831 Visits: 3290

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.



LinkedIn Profile

Newbie on www.simple-talk.com
MacroTrenz Consulting
MacroTrenz Consulting
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 1
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.



Mike Good
Mike Good
SSC Eights!
SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)

Group: General Forum Members
Points: 810 Visits: 1033

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.





Dan Sydner
Dan Sydner
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 10
Isn't Coalesce preferred over IsNull, being ansi standard and all? You wouldn't have had this problem anyway...
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