SQL Server 2008中使用CASE WHEN校验数据遇类型转换及溢出错误

嘿，我来帮你搞定这个问题！你遇到的两个报错——非数字字符串转数值失败、数值溢出，本质都是SQL Server的隐式类型转换在搞事情：

为什么会报错？ #

当你写th.idEmpleado IN(SELECT rhem.idEmpleado FROM ...)的时候，子查询返回的是数值类型（比如int或者smallint），SQL Server会自动把你的varchar列转换成对应的数值类型再做匹配。这时候如果varchar列里有非数字内容（比如'asdf'），转换直接失败；要是数值超出了目标类型的范围（比如'234324234'远大于smallint的最大值32767），就会触发溢出错误。

怎么解决？ #

在做关联校验之前，我们得先给这些varchar列做个合法性预检查，确保只有符合要求的数值才参与后续判断。因为SQL Server 2008没有TRY_CONVERT这种容错转换函数，我们用ISNUMERIC结合数值范围判断来实现：

修正后的校验查询语句

SELECT 
    th.idTempHorario, 
    th.idEmpleado, 
    th.nroDocumento, 
    th.dia, 
    th.idHorario, 
    th.idUsuario,
    -- 校验idEmpleado：先筛有效数字，再判断是否在INT范围内，最后匹配Empleado表
    CASE 
        WHEN ISNUMERIC(th.idEmpleado) = 1 
             AND CONVERT(BIGINT, th.idEmpleado) BETWEEN -2147483648 AND 2147483647 -- 适配INT类型范围
             AND CONVERT(INT, th.idEmpleado) IN(SELECT rhem.idEmpleado FROM rrhh.dbo.Empleado AS rhem) 
        THEN 1 
        ELSE 0 
    END AS validacionIdEmpleado,
    -- 校验nroDocumento
    CASE 
        WHEN ISNUMERIC(th.nroDocumento) = 1 
             AND CONVERT(BIGINT, th.nroDocumento) BETWEEN -2147483648 AND 2147483647
             AND CONVERT(INT, th.nroDocumento) IN(SELECT rhem.numTipoDocuIdent FROM rrhh.dbo.Empleado AS rhem) 
        THEN 1 
        ELSE 0 
    END AS validacionNroDocumento,
    -- 校验dia：假设目标列是SMALLINT，范围是-32768到32767
    CASE 
        WHEN ISNUMERIC(th.dia) = 1 
             AND CONVERT(BIGINT, th.dia) BETWEEN -32768 AND 32767
             AND CONVERT(SMALLINT, th.dia) IN(SELECT gd.idDia FROM General.dbo.dia AS gd) 
        THEN 1 
        ELSE 0 
    END AS validacionDia,
    -- 校验idHorario：根据目标表列的实际类型调整范围
    CASE 
        WHEN ISNUMERIC(th.idHorario) = 1 
             AND CONVERT(BIGINT, th.idHorario) BETWEEN -2147483648 AND 2147483647
             AND CONVERT(INT, th.idHorario) IN(SELECT rhas.idHorarioAdmin FROM rrhh.asistencia.horarioAdmin AS rhas) 
        THEN 1 
        ELSE 0 
    END AS validacionIdHorario
FROM database.dbo.temp_horario2 AS th

关键细节说明 #

1. 先用ISNUMERIC过滤非数字：把像'asdf'这种根本不是数字的内容直接排除，避免转换报错；
2. 用BIGINT做中间转换：如果直接转成目标类型（比如INT），大数值可能提前溢出，所以先转成范围更大的BIGINT，判断是否在目标类型的合法范围内，再转成目标类型做关联；
3. 匹配目标表列的类型范围：你需要根据rrhh.dbo.Empleado.idEmpleado、General.dbo.dia.idDia这些列的实际数值类型，调整BETWEEN的范围（比如SMALLINT的范围是-32768到32767，INT是-2147483648到2147483647）。

额外小建议 #

如果之后经常要做这类CSV导入校验，有两个优化方向：

• 导入时先做数据清洗，提前把非数字、超出范围的数值标记出来；
• 要是能升级到SQL Server 2012及以上版本，用TRY_CONVERT会更简洁省心，比如：

  -- SQL Server 2012+ 写法示例
  CASE 
      WHEN TRY_CONVERT(INT, th.idEmpleado) IS NOT NULL
           AND TRY_CONVERT(INT, th.idEmpleado) IN(SELECT rhem.idEmpleado FROM rrhh.dbo.Empleado AS rhem)
      THEN 1 ELSE 0 
  END AS validacionIdEmpleado
  

内容的提问来源于stack exchange，提问作者bryan-gc