SQL Serverで文字列を結合する際の注意点

/ 流通システム部　

千葉 (2010年6月29日 11:50)
流通システム部 / データベース

SQL Server2005以降、文字データ型nvarcharのサイズとして従来の1～4,000の他、
maxが指定可能になりました。

max(正確には2^31-1バイト)もあれば文字数の制限に悩まされる事はまずないと
考えていたのですが、以前、ストアドプロシージャの内部で動的に作成した
SQL文字列が4,000文字で途切れる、といった問題が発生しました。

以下に、例を挙げます。

DECLARE @sSql NVARCHAR(MAX)
SET @sSql = N'SELECT ' + REPLICATE(N'-', 4000)
PRINT 'LEN(@sSql):' + STR(LEN(@sSql))

出力結果をみると、切り捨てが行われているのが判ります。
LEN(@sSql): 4000

本来であれば、'SELECT '(7文字) + REPLICATE(N'-', 4000)(4000文字)で、
文字列長は4007となるはずです。

原因を調査したところ、Microsoftのリファレンスで次のように述べられていました。

「文字列の連結の結果が 8,000 バイトを超える場合、結果は切り捨てられます。」
引用元：http://msdn.microsoft.com/ja-jp/library/ms177561.aspx

つまり、格納先変数のサイズはmaxになっているのですが、格納元の文字列で結合を行った際に
暗黙的な型変換が発生し、nvarchar(4000)として扱われたため、切り捨てが発生していた模様です。

また、リファレンスには次のようにも記載されています。
「ただし、連結する文字列の少なくとも一方が大きな値の型の場合、切り捨ては行われません。」

具体的には、次のように記述します。

DECLARE @sSql NVARCHAR(MAX)
SET @sSql = ''
SET @sSql = @sSql + N'SELECT ' + REPLICATE(N'-', 4000)
--          ^^^^^
PRINT 'LEN(@sSql):' + STR(LEN(@sSql))

結合した文字列を直接変数に格納するのではなく、一度nvarchar(max)型の文字列と
結合することで、切り捨てを回避する事ができます。

結果は次のようになり、切り捨てが行われていない事が確認できます。
LEN(@sSql): 4007

比較的短いSQL文であれば問題はないのですが、ストアドプロシージャを用いて
動的にクエリを作成する場合、4000文字を超える事は少なくありません。
不要なバグの発生を抑えるためにも、頭の片隅にでも置いておくと良いでしょう。

「システム開発ブログ」へのコメントやご意見等ありましたら、ファンページまでお願いします。 Facebook のファンページ

[PR]
他社のシステムも直します。資産を活かしてシステムリフォーム

システムのトラブル解決を承ります。すぐにシステムリフォームを体験できます

システム開発のアイロベックス（東京都新宿区）

/ 流通システム部　  Tweet シェア

システム開発ブログ

最近の記事

カテゴリー

部署

月別アーカイブ

以前の部署別ブログ

検索

システム開発専用サイト

Windowsフォルダーアクセス権限確認ツール

Webとリアルをつなぐコンテンツマネージメントシステム

Twitter公式アカウントに認定されています