title: SSMS 12 SP comparing int and VarChar
body: <p>I have taken over a program and found that in one of my stored procedures, there is a SSIS job that fails randomly at the SP that reads the XML output of the Geocoding Service and parses the values into SQL fields, like this:</p>
  
  <pre><code>   INSERT #out_p_geocode_addresses
  --where my following SP's are looking for the successfully geocoded addresses
  (address_id int
  ,score int
  ,lat int
  ,lon int
  ,standard_address VARCHAR(max)
  ,geocode_match_flag bit
  )
  
  SELECT t.address_id
  ,CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionScore")]')) 
  ,CASE 
  --Retrieves the 'Y' geocode value and sets it to the 'lat' field
      WHEN ISNUMERIC(CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionShape")]/Y[1]'))) = 1
          THEN CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionShape")]/Y[1]'))
  END
  ,CASE 
  --Retrieves the 'X' geocode value and sets it to the 'lon' field
      WHEN ISNUMERIC(CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionShape")]/X[1]'))) = 1
          THEN CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionShape")]/X[1]'))
  END
  --sets value for the standard_address Field
  ,CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionMatch_addr")]')) 
  ,CASE 
  --sets the geocode_match_flag to 'Y' if the 'lon' field is numeric and not null or sets the value to 'N'
      WHEN ISNUMERIC(CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionShape")]/X[1]'))) = 1 
          THEN 'Y'
      ELSE 'N'
  ENDFROM #batch_p_geocode_addresses  AS t 
  JOIN    @GeocodeResultXML.nodes('//RecordSet/Records/Record') 
                                                  AS Records(Record)  
  ON t.address_id = CONVERT(VARCHAR(MAX),Records.Record.query('./Values/Value[sql:variable("@positionResultId")]'))
  </code></pre>
  
  <p>I have had an issue with this job randomly failing,and always after running the above step more than 100 times. However with the JOIN comparison, I cannot understand why it is working at all... </p>
  
  <p>Could anyone explain why this comparison is working, or why it could be randomly failing when calling this SP?</p>
  
  <p>Any help would be greatly appreciated.</p>