Solve the Problem of integer column overflow when varchar value is converted to '200'

Use the following code at the beginning:

alter proc getSerialNO@TYPE VARCHAR(2),@serialNO VARCHAR(10) OUTPUTASif (upper(@type) ='TR')begin select @serialNO = SERIALNO from tb_trserialno set @serialNO = @serialNO + 1 update tb_trserialno set SERIALNO = @serialNO ,LASTUPDATE = CONVERT(VARCHAR,GETDATE(),120)end

Error:

When the varchar value is converted to '123', the integer column is exceeded.

Analysis:
When the set @ serialno = @ serialno + 1 Statement is executed, the database should try to convert @ serialno to the default int for the + 1 operation. An exception occurred during the conversion!

You can find the information on the Internet:

Bigintbigint from
-2 ^ 63 (-9223372036854775808)
To 2 ^ 63-1
(9223372036854775807) of integer data (all numbers ). The storage size is
8 bytes. Modify as follows:

 

 

alter proc getSerialNO@TYPE VARCHAR(2),@serialNO VARCHAR(10) OUTPUTASif (upper(@type) ='TR')beginselect @serialNO = SERIALNO from tb_trserialnoset @serialNO = cast(cast(@serialNO as bigint) + 1 as varchar)update tb_trserialno set SERIALNO = @serialNO ,LASTUPDATE = CONVERT(VARCHAR,GETDATE(),120)end

 

Normal Test