SQL Server存储过程中的CASTING到INT问题

Question

我试图在SQL Server中运行的存储过程遇到错误。我的部分程序如下：

WHERE 
RM20101.CUSTNMBR NOT LIKE ('CR0002%')
AND RM20101.VOIDSTTS != 1
AND CAST(REPLACE(LEFT( RM20101.DOCNUMBR, CHARINDEX('-', RM20101.DOCNUMBR)), '-', '') AS INT) = 481492) a
WHERE 
a.DocumentAmount != 0

我在这里做的CAST导致了这个错误：

varchar值"02570000096"的转换溢出了一个int列。

所以我尝试使用BIGINT，就像这样：

WHERE 
RM20101.CUSTNMBR NOT LIKE ('CR0002%')
AND RM20101.VOIDSTTS != 1
AND CAST(REPLACE(LEFT( RM20101.DOCNUMBR, CHARINDEX('-', RM20101.DOCNUMBR)), '-', '') AS BIGINT) = 481492) a
WHERE 
a.DocumentAmount != 0

但这就产生了这个错误：

将数据类型varchar转换为bigint时出错。

如何以不会出错的方式处理此问题？

Answer 1

一行或多行包含无法转换的值。考虑使用TRY_CONVERT，或者使用where子句过滤掉坏行

您也可以考虑根本不转换数据，只查找WHERE column LIKE '481492-%'或类似的数据。不转换意味着您有更多机会使用索引

Answer 2

由于您删除了最后一个问题。

我怀疑你在DOCNUMBR 中有一些伪造或意外的数据

如果使用try_convert((，它将返回NULL值，而不是抛出错误

Declare @YourTable table (DOCNUMBR varchar(50))
Insert Into @YourTable values
('004000009123-000')
,('02570000096-000')
,('BOGUS-STRING')
,('BOGUS')
,(null)

Select *
,AsAnInt = try_convert(bigint,left(DOCNUMBR,charindex('-',DOCNUMBR+'-')-1))
From  @YourTable

退货

DOCNUMBR            AsAnInt
004000009123-000    4000009123
02570000096-000     2570000096
BOGUS-STRING        NULL         -- Fails to Convert but no error
BOGUS               NULL         -- Fails to Convert but no error
NULL                NULL         -- Fails to Convert but no error

查找故障记录

Select *
From  @YourTable
Where try_convert(bigint,left(DOCNUMBR,charindex('-',DOCNUMBR+'-')-1)) is null

退货

DOCNUMBR
BOGUS-STRING
BOGUS
NULL