varchar をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。

SQL Serverで頭記のエラーが起きたときは、文字列の数字を数値型の値に変換しようとして桁があふれた場合が多い。
たとえば '123456789012' という数字を０という数値と比較すると、数字を数値に変換して比較しようとして桁あふれが（算術オーバーフロー）が起きる

select 1 where '123456789012' = 0

SQLServer2014だとエラーメッセージが少し変わって下記のようになる。

varchar の値 '123456789012' の変換が int 型の列でオーバーフローしました。

数字を数値に変換するSQLを修正すれば、このエラーは消える。テーブル設計がひどくて、varchar型の列に数字を登録して数値として扱う運用をしていたりすると、なかなか簡単ではないかもしれない。

＊

ここからは事例の紹介。

SQL Serverで何気ないＳＱＬが急にエラーになってびっくりすることがある。たとえば、このＳＱＬは調子よく動いていたのだが、データによってエラーが起きて、あたふたしてしまった。

select case when isnull([機関番号],0) = 0 then 5521111111 else [機関番号] end

このSQLには１つ問題があって「機関番号」がvarchar(10)の列というのがミソ。ここに '5555555555' という大きな数字を登録したら、エラーが起きた。

エラーの原因を調べるためにSQLを少し書き換えてみる

SQL（１）
select case when isnull('5555555555', 0) = 0 then 5521111111 else 0 end
SQL（２）
select isnull('5555555555', 0)
SQL（３）
select case when isnull('5555555555', '') = '' then 5521111111 else 0 end
SQL(4)
select case when 1=0 then 5521111111 else '55555555555' end

SQL（１）エラーが出る。「varchar をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。」
SQL（２）結果は「5555555555」
SQL（３）結果は「0」
SQL（４）エラーが出る。「varchar をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。」

このエラーは '5555555555' という数字を int型の数値に変換しようとして起きているように思える。エラーが出ないように書き換えるとこうなる。

select case when cast(isnull([機関番号], 0) as numeric) = 0 then 5521111111 else cast([機関番号] as numeric)  end

まあ、「機関番号」はvarcharなのだから、数字まじりのSQLにしているのがよろしくないのだ。列の型に素直にＳＱＬを書くとこうなる。

select case when cast isnull([機関番号], '0') = '0' then '5521111111' else [機関番号] end

数値型の結果が欲しければ、こうする。

select cast(case when cast isnull([機関番号], '0') = '0' then '5521111111' else [機関番号] end as numeric)

*