SQL Server: Arithmetic overflow error converting expression to data type bigint

Question

Here's the sequence of my query:

SELECT
      CASE
         WHEN ((BarCode IS NOT NULL) AND (ExternelBarCode IS NULL)) THEN BarCode
         WHEN ((BarCode IS NULL) AND (ExternelBarCode IS NOT NULL)) THEN CAST(ExternelBarCode AS bigint)
         ELSE ExternelBarCode -- When both exist
      END AS TicketBarCode
...

When I run it, I've got the following message:

Arithmetic overflow error converting expression to data type bigint.

The column BarCode is of type bigint, and column ExternelBarCode is of type varchar(250).

BarCode = 178625624324,
ExternelBarCode = 2015591149641227565492

How can I resolve this issue?

Answer 1

The value 2,015,591,149,641,227,565,492 is greater than the maximum value allowed in bigint 2^63-1 (9,223,372,036,854,775,807) so the conversion is not possible.

If you need to return a numeric value, you can cast it to decimal(38,0)

SELECT
      CASE
         WHEN ((BarCode IS NOT NULL) AND (ExternelBarCode IS NULL)) THEN CAST(BarCode as decimal(38,0))
         WHEN ((BarCode IS NULL) AND (ExternelBarCode IS NOT NULL)) THEN CAST(ExternelBarCode AS decimal(38,0))
         ELSE cast(ExternelBarCode as decimal(38,0)) -- When both exist
      END AS TicketBarCode