算術オーバーフロー SQL Serverは、varcharのストアドプロシージャを自動的に切り捨てます。




データ型 varchar を numeric に変換中にエラーが発生しました。 (5)

1つの解決策は、

  1. すべての受信パラメータをvarchar(max)変更します。
  2. spのプライベート変数に正しいデータ長を設定する(すべてのパラメータをコピーして貼り付け、最後に "int"を追加する
  3. 変数名と同じ列名を持つ表変数を宣言する
  4. 各変数が同じ名前の列に入る行をテーブルに挿入します
  5. テーブルから内部変数を選択する

このようにして、既存のコードに対する変更は、以下のサンプルのように非常に小さくなります。

これが元のコードです:

create procedure spTest
(
    @p1 varchar(2),
    @p2 varchar(3)
)

これは新しいコードです:

create procedure spTest
(
    @p1 varchar(max),
    @p2 varchar(max)
)
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3)
insert into @test (p1,p2) varlues (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test

入力パラメータの長さが静かに文字列を切り落とすのではなく、制限を超える場合は、SQL Serverはエラーをスローします。

このフォーラムの議論によると、SQL Server(私は2005を使用していますが、これは2000年と2008年にも適用されます)は静的にvarcharの長さにストアドプロシージャのパラメータとして指定した任意のvarchar切り捨てます。 INSERTによって実際にはエラーが発生します。 例えば。 このテーブルを作成した場合:

CREATE TABLE testTable(
    [testStringField] [nvarchar](5) NOT NULL
)

私が次のことを実行すると、

INSERT INTO testTable(testStringField) VALUES(N'string which is too long')

私はエラーが発生します:

String or binary data would be truncated.
The statement has been terminated.

すばらしいです。 データの整合性が保持され、呼び出し元はそれについて知っています。 次に、それを挿入するストアドプロシージャを定義しましょう:

CREATE PROCEDURE spTestTableInsert
    @testStringField [nvarchar](5)
AS
    INSERT INTO testTable(testStringField) VALUES(@testStringField)
GO

それを実行する:

EXEC spTestTableInsert @testStringField = N'string which is too long'

エラーはなく、1行は影響を受けます。 testStringFieldを 'strin'として行がテーブルに挿入されます。 SQL Serverは、ストアドプロシージャのvarcharパラメータをvarchar切り捨てました。

さて、この行動は時には便利かもしれませんが、私はそれをオフにする方法はありません。 ストアドプロシージャに長すぎる文字列を渡すとエラーが発生するので 、これは非常に面倒です 。 これに対処するには2つの方法があるようです。

まず、ストアド@testStringField@testStringFieldパラメータをsize 6として宣言し、その長さが5を超えているかどうかを確認します。これはちょっとしたハックのように@testStringFieldますし、 @testStringFieldな定型コードを含んでいます。

次に、すべてのストアドプロシージャのvarcharパラメータをvarchar(max)に宣言し、ストアドプロシージャ内のINSERT文が失敗するようにします。

後者は正常に動作しているようですので、私の質問です:SQL Serverのストアドプロシージャの文字列にはvarchar(max)を使用することをお勧めします。 それはベストプラクティスでもありますか? 無効にすることはできませんサイレント切り捨ては私にはばかげているようです。


このような詳細な議論を引き出すために、いつものようにに感謝します。 私は最近、トランザクションへの標準的なアプローチとブロックの試行/捕捉を使用して、それらをより強固にするために私のStored Proceduresを精査しています。 私はJoe Stefanelli氏に、「私の提案はアプリケーション側に責任を持たせることだ」とJez氏は完全に同意します。「SQL Serverが文字列の長さを確認することが望ましい」 ストアドプロシージャを使用することの全ポイントは、データベースに固有の言語で書かれており、最後の防衛線として機能することです。 アプリケーション側では、255と256の差は無限ですが、データベース環境内で最大サイズが255のフィールドは256文字を受け入れません。 アプリケーションの検証メカニズムはできるだけバックエンドのdbを反映する必要がありますが、メンテナンスは難しいので、誤って不適切なデータをアプリケーションが誤って受け入れると、データベースが私に良いフィードバックを与えるようにしてください。 だから、私はCSVやJSONなどのテキストファイルの代わりにデータベースを使用しています。

なぜ私のSPの1つが8152のエラーをスローし、もう1つは静かに切り捨てられたのか困惑しました。 私は最終的につぶやきました:8152エラーを投げたSPには、関連するテーブルの列よりも1文字を多く許すパラメータがありました。 テーブルの列はnvarchar(255)に設定されていましたが、パラメータはnvarchar(256)でした。 だから、私の "間違い"がgbnの関心事である「大規模なパフォーマンスの問題」に対処しないのではないか? maxを使用する代わりに、おそらくテーブルの列サイズを一貫して255に設定し、SPパラメータをただ1文字、たとえば256に設定することができます。これにより、サイレントトランケーションの問題が解決され、パフォーマンス上のペナルティは発生しません。 おそらく私は考えていないいくつかの他の欠点がありますが、それは私にとって良い妥協のようです。

更新:私はこの技術が一貫していないのが怖いです。 さらにテストすると、8152エラーが発生することがあり、データが静かに切り捨てられることがあります。 誰かが私にこれに対処するより信頼できる方法を見つけるのを助けることができれば、私は非常に感謝します。

更新2:このページのPyitoechitoの答えをご覧ください。


同じ挙動がここに見られる:

declare @testStringField [nvarchar](5)
set @testStringField = N'string which is too long'
select @testStringField

私の提案は、アプリケーション側がストアドプロシージャを呼び出す前に入力の検証を担当するようにすることです。


更新:私はこの技術が一貫していないのが怖いです。 さらにテストすると、8152エラーが発生することがあり、データが静かに切り捨てられることがあります。 誰かが私にこれに対処するより信頼できる方法を見つけるのを助けることができれば、私は非常に感謝します。

これはおそらく、文字列の256番目の文字が空白であるために発生しています。 VARCHARは挿入時に末尾の空白を切り捨て、警告を生成します。 だから、ストアドプロシージャは静かに文字列を256文字に切り詰めています。そして、あなたの挿入は末尾の空白を(警告付きで)切り詰めています。 前記文字が空白でない場合、エラーが発生します。

おそらく解決策は、ストアド・プロシージャのVARCHAR空白以外の文字をキャッチするのに適した長さにすることです。 VARCHAR(512)はおそらく十分安全です。


それだけです

私のチェックの一つは、私のパラメータが私のテーブルの列の長さに一致することを確認することになるので、私は問題に気づいたことはありません。 クライアントコードでも。 個人的には、私はSQLが長すぎるデータを見ることはないと思っています。 私が切り捨てられたデータを見た場合、それが原因で何が起こったのかは明らかです。

あなたがvarchar(max)の必要性を感じるなら、 データ型の優先順位のために大規模なパフォーマンスの問題に注意してください。 varchar(max)はvarchar(n)よりも優先順位が高くなります(最長が最高です)。 したがって、このタイプのクエリでは、シークではなく、すべてのvarchar(100)値がCASTからvarchar(max)までのスキャンを取得します。

UPDATE ...WHERE varchar100column = @varcharmaxvalue

編集:

この問題に関するMicrosoft Connectのオープンアイテムがあります

そして、おそらく、 Erland SommarkogのStrict設定 (および一致するConnect項目 )に含める価値があります

編集2、マーティンズのコメント後:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql = 'B', @nsql = 'B'; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;




truncation