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 Thursday, July 14, 2005 8:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #201080
Posted Thursday, July 14, 2005 1:47 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 16, 2006 1:20 AM
Points: 92, 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
Post #201251
Posted Thursday, July 14, 2005 1:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
They are talking about the function ISNULL not the operand "is null".
Post #201259
Posted Thursday, July 14, 2005 2:02 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 16, 2006 1:20 AM
Points: 92, 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
Post #201265
Posted Thursday, July 14, 2005 2:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
That's been said too (buried somewhere in the first posts (antares)) .
Post #201267
Posted Thursday, July 14, 2005 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 2, 2009 3:01 AM
Points: 13, 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
Post #201270
Posted Thursday, July 14, 2005 4:33 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 16, 2006 1:20 AM
Points: 92, 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
Post #201316
Posted Friday, July 15, 2005 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #201592
Posted Friday, June 13, 2008 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2008 4:51 AM
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.
Post #516545
Posted Monday, July 27, 2009 6:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 3:56 PM
Points: 315, 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
Post #760421
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse