Error converting 'None' to int

• bcronce

  SSCrazy

  Points: 2597

  More actions

  September 16, 2008 at 10:08 am

  #212696

  I'm trying to match students from two different databases. The problem that's happening is that some grades a 'None', which will throw an error if it trys to convert it to an int.

  select m.iuserid

  from MatchStudents m

  inner join Students s on m.vchFirstname = s.vchfirstname

  and m.vchlastname = s.vchlastname

  and m.vchschoolid = s.vchschoolid

  and (m.chgender = s.chgender or m.chgender = 'U' or s.chgender = 'U')

  and (m.dtbirthdate = s.dtbirthdate or m.dtbirthdate is null or s.dtbirthdate is null)

  and ((isnumeric(m.vchGrade) = 1 and isnumeric(s.vchGradeLevelID) = 1

  and (convert(int,s.vchGradeLevelID) between (convert(int,m.vchGrade) -1) and (convert(int,m.vchGrade) +1)))

  or (isnumeric(m.vchGrade) = 0 or isnumeric(s.vchGradeLevelID) = 0))

  But what I'm not understanding is that I have that comparison seperated by an OR statement

  and (

  (

  isnumeric(m.vchGrade) = 1 and isnumeric(s.vchGradeLevelID) = 1

  and (convert(int,s.vchGradeLevelID) between (convert(int,m.vchGrade) -1) and (convert(int,m.vchGrade) +1))

  )

  or (isnumeric(m.vchGrade) = 0 or isnumeric(s.vchGradeLevelID) = 0)

  )

  The error only happens when I put in the "or (isnumeric(m.vchGrade) = 0 or isnumeric(s.vchGradeLevelID) = 0)". From what I understand is that once "isnumeric(m.vchGrade) = 1 and isnumeric(s.vchGradeLevelID) = 1" is false, the convert statements should not execute, which they don't without the "or". But for some reason, the convert statements are executing even though "isnumeric(m.vchGrade) = 1 and isnumeric(s.vchGradeLevelID) = 1" is returning false if I put the "or" in.

  I though SQL stops executing an AND statement once 1 comparison returns false.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 16, 2008 at 10:56 am

  #872550

  bcronce (9/16/2008)

  ---

  I though SQL stops executing an AND statement once 1 comparison returns false.

  Depends on the plan the optimiser comes up with. Sometimes it does, sometimes it doesn't. The order that the operands execute is not related to the order in which they are written.

  Perhaps a case statement will help you?

  convert(int,CASE m.vchGrade WHEN 'None' THEN 0 ELSE m.vchGrade END)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass