An Is Null Gotcha

• Dan Sydner

  SSC Enthusiast

  Points: 105

  More actions

  July 14, 2005 at 2:10 pm

  #574576

  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

  Say Hey Kid

  Points: 688

  More actions

  July 14, 2005 at 4:33 pm

  #574615

  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

  SSC-Insane

  Points: 23224

  More actions

  July 15, 2005 at 8:14 am

  #574851

  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

  Newbie

  Points: 9

  More actions

  June 13, 2008 at 4:51 am

  #828358

  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

  Old Hand

  Points: 389

  More actions

  July 27, 2009 at 6:09 pm

  #1030793

  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