2018年3月10日土曜日

SQL-Serverに、明細データが格納されたパラメータを効率よく渡す方法

このところ、色々と勉強している中で、悩んでいる事が少なからずあります。

その中の一つが「SQL-Serverに、明細データが格納されたパラメータを効率よく渡すにはどういう方法が良いか?」というものがあるので、ちょっとこのブログに記事を書かせていただきたいと思います。

うーんと、明細データというのは、数字が1つや2つのデータじゃなくて、多数の行と列にデータが何十個も何百個もぎっしり詰まったものです。

これを、パラメータで簡単に受け渡ししたいなぁと。

SQL-Serverの内部だけの話なら、別にどうにでもなりそうですが、設計によっては、ブラウザ側対SQL-Serverという感じになったりします。

ブラウザ側から入力された明細データを、シンプルにSQL-Serverにポーンと渡したいなぁとか。

何か良いやり方はないかな。

とりあえずは、たとえば、XML型を使って、文字列で明細データを受け渡しするしかないかな?

以下にこんな感じ・・・というSQLを書いてみました。




↓XML型のテストデータ等がブログでは表現できませんでした。 
DECLARE @xValue AS xml  --XML型
       ,@sMES   AS nvarchar(255); --エラーメッセージ
--外部から渡される、XMLによる明細データ・・・を想定
SET @xValue = N'' +
              N'    10000プレイステーション39800'+
     N'    20000ヴィータ19800'+
     N'';
BEGIN TRY
    --XMLをテーブルに変換(取得のみ。型チェックは後回し)
 IF EXISTS(SELECT TOP 1 * 
                    FROM tempdb.dbo.sysobjects WITH(NOLOCK) 
                   WHERE id=OBJECT_ID('tempdb.dbo.#TEST1'))
 BEGIN
     DROP TABLE #TEST1;
 END;
    SELECT row.value('C1 [1]','nvarchar(255)') AS GAME_CD
          ,row.value('C2 [1]','nvarchar(255)') AS GAME_NAME
       ,row.value('C3 [1]','nvarchar(255)') AS GAME_PRICE
   INTO #TEST1
      FROM @xValue.nodes('/ROOT/ROW') AS xTable(row);
    
 --簡易型チェック
    IF EXISTS(SELECT TOP 1 * 
             FROM #TEST1
      WHERE ISNUMERIC(GAME_CD) = 0 OR
            ISNUMERIC(GAME_PRICE) = 0)
    BEGIN
     RAISERROR(N'コード番号または値段に不正な値が入力されています',16,1);
 END;

 --{以降の処理をここに記述する・・・}

END TRY
BEGIN CATCH
    SET @sMES = ERROR_MESSAGE();
    THROW 50000,@sMES,1;
END CATCH;


XML型は、パフォーマンスの問題や、トラブルを乗り越えた経験が自分に乏しいので、「プログラムを書けるは書けるけど、動かしてみて実用性はどうなのか?」というのがよく分かってなくて、まだまだ悩みは続きそうです。
時刻: