Azure Synapse Serverless SQL varchar转BIGINT报错排查

在Azure Synapse Serverless SQL环境执行SQL代码时，触发报错：
Error converting data type varchar to bigint.（将数据类型varchar转换为bigint出错）

执行的原始SQL代码如下：

;WITH CTE1 AS
 (
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account]
 ),CTE2 AS
 (
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]
 )
 SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
       ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
       ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking 
       ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow 
       ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship
 FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum

• 核心问题为字段类型不匹配：经排查ts_primarysecondaryfocus字段在基表dbo.account中为BIGINT类型，但在关联的dbo.OptionsetMetadata对象（表/视图）中为VARCHAR类型。
• 原代码中ISNULL(C2.ts_primarysecondaryfocus,'')、ISNULL(C2.ts_ukrow,'')的第二个参数传入空字符串（VARCHAR类型），和C1侧BIGINT类型的同名字段做不等值比较、CASE分支跨类型返回值时，SQL引擎会触发隐式类型转换，将VARCHAR值尝试转为BIGINT，当VARCHAR字段存在无法转换为数字的内容时，就会抛出该转换错误。
• 注：如果ts_ukrow字段在两表中也存在类型不一致的问题，会触发同类报错。

方案1：修改SQL代码做显式类型转换（无需调整表/视图结构，快速生效） #

对齐两侧字段的类型，避免隐式转换，同时用TRY_CAST处理转换异常，修改后的代码参考如下：

;WITH CTE1 AS
 (
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account]
 ),CTE2 AS
 (
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]
 )
 SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
       ,CASE 
            WHEN C1.ts_primarysecondaryfocus <> TRY_CAST(ISNULL(C2.ts_primarysecondaryfocus,'0') AS BIGINT)
            THEN TRY_CAST(C2.ts_primarysecondaryfocus AS BIGINT) 
            ELSE C1.ts_primarysecondaryfocus 
        END AS ts_primarysecondaryfocus
       ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking 
       ,CASE 
            WHEN C1.ts_ukrow <> TRY_CAST(ISNULL(C2.ts_ukrow,'0') AS BIGINT)
            THEN TRY_CAST(C2.ts_ukrow AS BIGINT) 
            ELSE C1.ts_ukrow 
        END AS ts_ukrow 
       ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship
 FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum

如果业务上需要保留字段为字符串类型，把C1侧的BIGINT字段显式转为VARCHAR即可，注意ISNULL的默认值要匹配对应类型。

方案2：调整表/视图结构统一字段类型（长期规范方案） #

根据业务实际存储内容统一两侧字段类型：

• 如果确认ts_primarysecondaryfocus、ts_ukrow存储的都是数值型选项值，修改dbo.OptionsetMetadata中对应字段的类型为BIGINT；如果该对象是视图，调整视图定义，将对应字段显式转换为BIGINT后输出，和基表类型保持一致。
• 如果字段实际存在非数值的合法内容，修改基表侧对应字段的类型为VARCHAR，和元数据表类型对齐，从根源避免隐式转换问题。

排查参考：可通过系统视图查询字段类型确认匹配关系，相关字段类型差异可参考对应排查截图。

内容的提问来源于stack exchange，提问作者Patterson