SQLServerの再帰SQLで最大再帰数の指定

SQLServerで再帰SQLを実行すると最大再帰数に達してエラーになる現象が発生しました。

そのエラーの回避法を検討します。

再帰SQLを実行すると最大再帰数に達してエラー
1. 最大再帰数「MAXRECURSION」を使う
2. 最大再帰数「MAXRECURSION」の指定方法

再帰SQLを実行すると最大再帰数に達してエラー

先日作成した記事で

SQLServerの再帰SQLで歯抜けの日付データを取得する

日付を管理しているテーブルから共通テーブル式と再帰SQLを使って歯抜けの日付データを取得する

エラーが発生しました。

日付を１年分にして再帰SQLを実行すると「ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。」とエラーが発生します。

どうやら無限ループ等が発生した場合のことを考慮して再帰SQLでは再起の出来る回数の上限値を設定されているようです。

この上限値を変更するには「MAXRECURSION」を指定します。

最大再帰数「MAXRECURSION」を使う

設定方法は

    OPTION ( MAXRECURSION 0 )

で上限値を設定します。

設定できる値は「0」から「32,767」までで、「0」は上限なしになります。

規定値は「100」です。

最大再帰数「MAXRECURSION」の指定方法

実際の指定方法は

WITH cal_info AS
(
SELECT
CAST( ‘2013/01/01’ AS DATE ) AS cal_date
UNION ALL
SELECT
DATEADD( day, 1, cal_date )
FROM
cal_info
WHERE
cal_info.cal_date < '2013/12/31' ) SELECT CONVERT ( VARCHAR(10), cal_date, 111 ) AS カレンダー日付 ,CONVERT ( VARCHAR(10), chk_tbl.chk_date, 111 ) AS チェック日付 FROM cal_info LEFT OUTER JOIN 日付抜けをチェックするテーブル AS chk_tbl ON cal_info.cal_date = chk_tbl.chk_date AND chk_tbl.chk_date BETWEEN '2013/01/01' AND '2013/12/31' OPTION ( MAXRECURSION 365 ) [/sql] になります。