SQL转换错误调试求助：nvarchar值'DIST00'转int失败如何解决？

问题情况 #

执行目标SQL语句时，触发报错：将nvarchar值'Dist00'转换为int失败，原SQL语句如下：

SELECT g.[Period], g.BuyingGroup, g.NonJDEGroup, g.GroupCode, g.Percentage,
    c.DistributorId as CustomerNumber, 
    ci.CustomerName,
    ri.ShipTo,
    ci.ShipToTiedTo, 
    b.BidId,
    b.Account,
    ri.InvoiceNumber, 
    ri.InvoiceDate, 
    ri.DateModified, 
    i.ItemId,
    CASE WHEN ri.NetBilled=0 THEN round(ri.SalePrice * ri.Quantity, 2) ELSE 0 END AS SaleTotal, 
    CASE WHEN ri.NetBilled=1 THEN round(ri.BidPrice * ri.Quantity, 2) ELSE 0 END AS BidTotal, 
    CASE WHEN ri.SalePrice <= ri.BidPrice THEN 0 ELSE (ri.SalePrice - ri.BidPrice) * ri.Quantity END AS RebateTotal,
    ri.NetBilled,
    ri.Quantity,
    b.Bid_TypeId,
    b.Bid_ClassId,
    CAST(CASE WHEN (ri.DateModified >= @SalesStartDate and ri.DateModified <= @SalesEndDate) THEN 1 ELSE 0 END AS bit) AS isCurrent,
    CAST(CASE WHEN (ri.DateModified >= @LateStartDate and ri.DateModified <= @LateEndDate) THEN 1 ELSE 0 END AS bit) AS isLate 
FROM [BQM_Dev].dbo.Claims c 
    INNER JOIN [BQM_Dev].dbo.Claim_Items i ON c.ClaimId=i.ClaimId
    INNER JOIN [BQM_Dev].dbo.Rebate_Items ri ON ri.Claim_ItemId = i.Claim_ItemId
    INNER JOIN [BQM_Dev].dbo.Bids b on b.BidId = i.BidId 
    INNER JOIN [BQM_Dev].dbo.Bid_Classes bc ON bc.Bid_ClassId=b.Bid_ClassId
    LEFT OUTER JOIN #CustomerInformation ci ON ci.CustomerNumber=c.DistributorId AND ci.RecordType='C'
    LEFT OUTER JOIN #CustomerInformation si ON si.CustomerNumber=c.DistributorId AND si.RecordType IN ('SF','BH')
    INNER JOIN [BQM_Dev].dbo.FunctionalGroupings g on b.DistributorId=g.CustomerNumber
WHERE bc.IncludeInReporting=1 
    AND ri.DateModified between @LateStartDate and @SalesEndDate
    AND ri.InvoiceDate > @SalesCutOffDate

经排查，错误根源是连接条件的字段类型不匹配：

• ci.CustomerNumber为int类型，c.DistributorId为nvarchar类型，直接连接时SQL Server会自动尝试将nvarchar转int，遇到'Dist00'这类非数字字符串时转换失败
• b.DistributorId为nvarchar类型，g.CustomerNumber为int类型，同样存在自动转换失败的问题

修正方案 #

使用TRY_CONVERT函数（SQL Server 2012及以上版本支持）将nvarchar类型字段转换为int类型，该函数遇到无法转换的值时会返回NULL，而非直接中断查询。修改后的完整SQL如下：

SELECT g.[Period], g.BuyingGroup, g.NonJDEGroup, g.GroupCode, g.Percentage,
    c.DistributorId as CustomerNumber, 
    ci.CustomerName,
    ri.ShipTo,
    ci.ShipToTiedTo, 
    b.BidId,
    b.Account,
    ri.InvoiceNumber, 
    ri.InvoiceDate, 
    ri.DateModified, 
    i.ItemId,
    CASE WHEN ri.NetBilled=0 THEN round(ri.SalePrice * ri.Quantity, 2) ELSE 0 END AS SaleTotal, 
    CASE WHEN ri.NetBilled=1 THEN round(ri.BidPrice * ri.Quantity, 2) ELSE 0 END AS BidTotal, 
    CASE WHEN ri.SalePrice <= ri.BidPrice THEN 0 ELSE (ri.SalePrice - ri.BidPrice) * ri.Quantity END AS RebateTotal,
    ri.NetBilled,
    ri.Quantity,
    b.Bid_TypeId,
    b.Bid_ClassId,
    CAST(CASE WHEN (ri.DateModified >= @SalesStartDate and ri.DateModified <= @SalesEndDate) THEN 1 ELSE 0 END AS bit) AS isCurrent,
    CAST(CASE WHEN (ri.DateModified >= @LateStartDate and ri.DateModified <= @LateEndDate) THEN 1 ELSE 0 END AS bit) AS isLate 
FROM [BQM_Dev].dbo.Claims c 
    INNER JOIN [BQM_Dev].dbo.Claim_Items i ON c.ClaimId=i.ClaimId
    INNER JOIN [BQM_Dev].dbo.Rebate_Items ri ON ri.Claim_ItemId = i.Claim_ItemId
    INNER JOIN [BQM_Dev].dbo.Bids b on b.BidId = i.BidId 
    INNER JOIN [BQM_Dev].dbo.Bid_Classes bc ON bc.Bid_ClassId=b.Bid_ClassId
    -- 转换c.DistributorId为int类型后再连接
    LEFT OUTER JOIN #CustomerInformation ci ON ci.CustomerNumber = TRY_CONVERT(int, c.DistributorId) AND ci.RecordType='C'
    LEFT OUTER JOIN #CustomerInformation si ON si.CustomerNumber = TRY_CONVERT(int, c.DistributorId) AND si.RecordType IN ('SF','BH')
    -- 转换b.DistributorId为int类型后再连接
    INNER JOIN [BQM_Dev].dbo.FunctionalGroupings g ON g.CustomerNumber = TRY_CONVERT(int, b.DistributorId)
WHERE bc.IncludeInReporting=1 
    AND ri.DateModified between @LateStartDate and @SalesEndDate
    AND ri.InvoiceDate > @SalesCutOffDate

关键注意事项 #

1. TRY_CONVERT与CONVERT的区别：如果使用CONVERT(int, c.DistributorId)，遇到非数字字符串仍会触发报错；TRY_CONVERT则返回NULL，对于LEFT JOIN来说，对应关联表的字段会显示NULL，不会中断查询执行。
2. INNER JOIN的过滤影响：针对g表的INNER JOIN，如果b.DistributorId无法转换为int，该行数据会被过滤。若需要保留这类数据，可考虑改为LEFT JOIN，或提前清理b.DistributorId中的非数字值。
3. 长期优化建议：统一所有关联字段的数据类型（例如将所有CustomerNumber类字段改为nvarchar），从根源避免类型转换问题。

内容的提问来源于stack exchange，提问作者Paul T. Rykiel