将varchar值转换为int时出现转换失败

问题描述：

Microsoft SQL Server 2008（SP1），出现意外的“转换失败”错误。将varchar值转换为int时出现转换失败

不太清楚如何描述这个问题，所以下面是一个简单的例子。 CTE使用搜索条件提取某些ID的数字部分，以确保数字部分实际存在。然后，CTE被用于找到（种）的最低未使用的序列号：

CREATE TABLE IDs (ID CHAR(3) NOT NULL UNIQUE); 

INSERT INTO IDs (ID) VALUES ('A01'), ('A02'), ('A04'), ('ERR'); 

WITH ValidIDs (ID, seq) 
AS 
(
SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) 
    FROM IDs 
    WHERE ID LIKE 'A[0-9][0-9]' 
) 
SELECT MIN(V1.seq) + 1 AS next_seq 
    FROM ValidIDs AS V1 
WHERE NOT EXISTS (
        SELECT * 
        FROM ValidIDs AS V2 
        WHERE V2.seq = V1.seq + 1 
       );

该错误是，RR“转换VARCHAR值时，转换失败‘’为int数据类型”。

我不明白为什么值ID = 'ERR'应该考虑转换，因为谓词ID LIKE 'A[0-9][0-9]'应该已经从结果集中删除了无效行。

当基台取代有一个等效CTE问题消失即

WITH IDs (ID) 
AS 
(
SELECT 'A01' 
UNION ALL 
SELECT 'A02' 
UNION ALL 
SELECT 'A04' 
UNION ALL 
SELECT 'ERR' 
), 
ValidIDs (ID, seq) 
AS 
(
SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) 
    FROM IDs 
    WHERE ID LIKE 'A[0-9][0-9]' 
) 
SELECT MIN(V1.seq) + 1 AS next_seq 
    FROM ValidIDs AS V1 
WHERE NOT EXISTS (
        SELECT * 
        FROM ValidIDs AS V2 
        WHERE V2.seq = V1.seq + 1 
       );

为什么一个基本表引起该错误？这是一个已知的问题？

UPDATE @sgmoore：无，做在一个CTE过滤并且在另一个CTE铸造仍然会导致相同的错误例如

WITH FilteredIDs (ID) 
AS 
(
SELECT ID 
    FROM IDs 
    WHERE ID LIKE 'A[0-9][0-9]' 

), 
ValidIDs (ID, seq) 
AS 
(
SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) 
    FROM FilteredIDs 
) 
SELECT MIN(V1.seq) + 1 AS next_seq 
    FROM ValidIDs AS V1 
WHERE NOT EXISTS (
        SELECT * 
        FROM ValidIDs AS V2 
        WHERE V2.seq = V1.seq + 1 
       );

核心CTE工作正常 - 通过使用“SELECT * FROM ValidIDs”而不是您的查询来尝试它。错误必须在其他地方... – 2010-05-04 11:21:03

@marc_s：我同意CTE不是问题的根源。我不确定CTE是如何工作的，但我希望CTE的查询只是在解析时扩展到查询中。事实上，使用两个派生表（当然，每个语法都完全相同）重写查询仍然会出现问题。 – onedaywhen 2010-05-04 11:35:08

答

这是一个错误，已经被报告为SQL Server should not raise illogical errors（正如我所说，这是很难形容这一个！）通过Erland Sommarskog。

来自SQL Server可编程团队的回应是，“问题在于SQL Server由于在查询执行过程中推送推理/表达式而不考虑查询的逻辑结果而急切地引发错误。

现在我已经投了一个解决方法，大家都做同样的请:)

也可以在这个Q中看到甲骨文。https://stackoverflow.com/q/46287236/73226 – 2017-09-18 20:11:10

答

更换什么，如果部分

SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) 
FROM IDs 
WHERE ID LIKE 'A[0-9][0-9]'

随着

SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER) 
FROM 
(
    select ID from IDs 
    WHERE ID LIKE 'A[0-9][0-9]' 
)

我得到同样的错误。看到我的编辑问题。 – onedaywhen 2010-05-04 12:14:39

很奇怪，不，我不明白。该错误似乎是在哪里V2.seq = V1.seq + 1 将其更改为 WHERE isnumeric（v2.seq）= 1且isnumeric（v1.seq）= 1且V2.seq = V1。 seq + 1 作品（对我而言） – sgmoore 2010-05-04 12:47:09

答

这发生在我身上，因为我做了一个联盟，是不小心，以确保这两个查询了各自领域以相同的顺序。一旦我解决了这个问题，那就很好。

将varchar值转换为int时出现转换失败

相关推荐