Arithmetic overflow error

• ziangij

  SSCertifiable

  Points: 7986

  More actions

  December 2, 2009 at 2:05 am

  #136439

  Hi,

  foll. is my query:

  declare @tbl1 table

  (

  col1 smallint

  )

  insert into @tbl1

  select 1 union all

  select 2 union all

  select 3 union all

  select -2455123

  select * from @tbl1

  this throws an error: Arithmetic overflow error for data type smallint, value = -2455123.

  Is there a way like int.tryparse in Dotnet so that i get a default value of 0 in case of any issue and all the records are inserted. Is there any option apart from using try catch.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  December 2, 2009 at 4:15 am

  #1086351

  Hi,

  In the sql, it’s not possible to convert the overflow (if happened) to zero, but we have to make it to zero, like (in your case)

  declare @tbl1 table

  (

  col1 smallint

  )

  insert into @tbl1

  select (case when value between -32768/*Min range of the data type*/ and 32768/*Max range of the Data type*/ then value else 0 end) from

  (

  select 1 value union all

  select 2 union all

  select 3 union all

  select -2455123

  ) AS XX

  select * from @tbl1