Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

An Is Null Gotcha Expand / Collapse
Author
Message
Posted Friday, June 18, 2004 6:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 2,902, Visits: 1,818
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
Post #121958
Posted Wednesday, July 14, 2004 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 21, 2006 10:09 AM
Points: 3, 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
Post #126219
Posted Wednesday, July 14, 2004 3:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 3:30 PM
Points: 80, Visits: 2
That's not a gotcha, that's a "Man you wasted valuable development hours to figure that out!"
Post #126271
Posted Wednesday, July 14, 2004 3:28 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Today @ 7:25 AM
Points: 8,369, Visits: 740

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.




Post #126272
Posted Wednesday, July 14, 2004 6:27 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 6,266, Visits: 2,029

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
Post #126298
Posted Wednesday, July 14, 2004 6:37 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:16 AM
Points: 6,784, Visits: 1,895
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
Post #126300
Posted Thursday, July 15, 2004 1:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 2,902, Visits: 1,818

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
Post #126322
Posted Thursday, July 15, 2004 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 19, 2004 2:00 PM
Points: 131, 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.


Post #126391
Posted Sunday, August 1, 2004 3:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:30 AM
Points: 316, Visits: 814

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.




Post #129490
Posted Thursday, July 14, 2005 4:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 2, 2009 3:01 AM
Points: 13, Visits: 10
Isn't Coalesce preferred over IsNull, being ansi standard and all? You wouldn't have had this problem anyway...
Post #200949
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse