The conversion of the varchar value overflowed an int column

For some value of @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.

3 Answers

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.

Solution

Declare @phoneNumber bigint

Reference

Thanks Ravi and other users .... Nevertheless I have got the solution

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

Just make rdg2.nPhoneNumber varchar everywhere instead of int !