varchar值的转换溢出了int列

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列。

2 个解决方案

#1

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)

如

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

Reference

参考

#2

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

#1

Declare @phoneNumber int

select @phoneNumber=Isnull('08041159620',0);