SQL varchar 值的转换溢出了一个 int 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15288186/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The conversion of the varchar value overflowed an int column
提问by Surajit Biswas
For some value of @nReservationID:
对于某些值@nReservationID：
SELECT @phoneNumber=
CASE
WHEN ISNULL(rdg2.nPhoneNumber ,0) = 0 THEN ISNULL(rdg2.nMobileNumber, 0)
ELSE ISNULL(rdg2.nPhoneNumber ,0)
END
from tblReservation_Details_Guest rdg2
where nReservationID=@nReservationID
Why do I get this error?
为什么我会收到这个错误？
Msg 248, Level 16, State 1, Procedure USP_CRE_WEB_MEMBER_AUTOGENERATEDECV, Line 136 The conversion of the varchar value '08041159620' overflowed an int column.
消息 248，级别 16，状态 1，过程 USP_CRE_WEB_MEMBER_AUTOGENERATEDECV，第 136 行 varchar 值“08041159620”的转换溢出了 int 列。
采纳答案by Surajit Biswas
Thanks Ravi and other users .... Nevertheless I have got the solution
感谢 Ravi 和其他用户......不过我已经找到了解决方案
SELECT @phoneNumber=
CASE
WHEN ISNULL(rdg2.nPhoneNumber ,'0') in ('0','-',NULL)
THEN ISNULL(rdg2.nMobileNumber, '0')
WHEN ISNULL(rdg2.nMobileNumber, '0') in ('0','-',NULL)
THEN '0'
ELSE ISNULL(rdg2.nPhoneNumber ,'0')
END
FROM tblReservation_Details_Guest rdg2
WHERE nReservationID=@nReservationID
Just need to put '0' instead of 0
只需要把 '0' 而不是 0
回答by Ravi Singh
Declare @phoneNumber int
select @phoneNumber=Isnull('08041159620',0);
Give error :
给出错误：
The conversion of the varchar value '8041159620' overflowed an int column.: select cast('8041159620' as int)
AS
作为
Integer is defined as :
整数定义为：
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.
从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整数（整数）数据。存储大小为 4 个字节。int 的 SQL-92 同义词是整数。
Solution
解决方案
Declare @phoneNumber bigint
回答by Schoep
Just make rdg2.nPhoneNumber varchar everywhere instead of int !
只需让 rdg2.nPhoneNumber 到处都是 varchar 而不是 int ！
