PR
カレンダー
カテゴリ
カテゴリ未分類
(2)DB-SQL Server 2012
(7)DB-SQL Server 2008
(23)DB-SQL Server
(27)DB-Oracle
(4)DB-TransactSQL
(32)DB-PL/SQL
(6)資格
(31)その他
(16)2026.05
2026.04
2026.03
2026.04
2026.03
2026.02
2026.01
2026.01
キーワードサーチ
▼キーワード検索
ジャムシード
Database Developerという肩書を得るために日々格闘する文系エンジニアのブログ
コメント新着
2009.05.09
カテゴリ： DB-TransactSQL
「インサイド SQL Server 2005」シリーズは４冊ある。
・ストレージエンジン編
・クエリチューニング＆最適化編
・T-SQLクエリ編
・T-SQLプログラミング編
しかし、邦訳が出版されたのは上の２冊だけで、あとの２冊は結局邦訳されなかった。
そこで「T-SQLプログラミング編」を原書で購入して読んでいた。やはりすごい本だ。
この中で、「ストアドプロシージャのパラメータをXMLで受け渡すこと」について言及されていてハッとした。
SQL Server2008ではテーブル値パラメータがサポートされたが、それまではストアドプロシージャにはスカラー形式でのパラメータしか渡すことができなかった。実際にフロントエンドにAccessや.NETのグリッドなどを使っていると、複数セットのデータをパラメータとして受け渡したいことがよく出てくる。
ワンセットであれば、以下のようなパラメータでもいい。
EXEC sp_UpdateInfo @CustomerID,@TradeID,@Date,@ProdCD,@Amount
しかし、パラメータの数が多かったり、複数セットの場合はバラバラのパラメータを用意していてはキリがないので、パラメータは１つにしてデリミタで区切って渡したりする。
EXEC sp_UpdateInfo @PARM
この@PARMに「12345678,12,2008/12/31,101,25!222345678,12,2009/01/04,102,20」などという値をセットすることになるわけだ。
一方、ストアドプロシージャ側でもこれを区切ってテーブルに戻すために、「デリミタ区切りの文字列をテーブル化する」ようなテーブル関数を予め準備しておく必要がある。仮にこれをsf_TOTABLEとすると、テーブル化するのには意外と手間がかかる。
DECLARE D_CUR CURSOR LOCAL FOR
SELECT ITEM FROM sf_TOTABLE(@PARM,'!')
ORDER BY SEQ
OPEN D_CUR
FETCH NEXT FROM D_CUR INTO @ITEM
BEGIN
INSERT INTO #TABLE
SELECT
MAX(CASE WHEN SEQ=1 THEN ITEM END) CustomerID,
MAX(CASE WHEN SEQ=2 THEN ITEM END) TradeID,
MAX(CASE WHEN SEQ=4 THEN ITEM END) ProdCD,
MAX(CASE WHEN SEQ=5 THEN ITEM END) Amount
FROM sf_TOTABLE(@ITEM,',')
FETCH NEXT FROM D_CUR INTO @ITEM
END
CLOSE D_CUR
DEALLOCATE D_CUR
実際にこの方法で受け渡しを行ったりしているのだが、やはりスマートとはいい難い。二重のデリミタに加えて、「何カラム名が何の項目か」も分からない状態で受け渡しをすることになる。
では、XMLをパラメータにするとどうなるか。
パラメータの内容は以下の形になるだろう。
＜Parameters＞
＜CustomerID＞12345678＜/CustomerID＞
＜TradeID＞12＜/TradeID＞
＜Date＞2008/12/31＜/Date＞
＜ProdCD＞101＜/ProdCD＞
＜Amount＞25＜/Amount＞
＜/Parameters＞
＜Parameters＞
＜CustomerID＞22345678＜/CustomerID＞
＜TradeID＞13＜/TradeID＞
＜Date＞2009/01/04＜/Date＞
＜ProdCD＞102＜/ProdCD＞
＜Amount＞20＜/Amount＞
＜/Parameters＞
EXEC sp_UpdateInfo @PARM
受け取ったストアドプロシージャ側では@PARMという１つのパラメータを受け取るにも関わらず、それぞれの項目が何かも把握できる。
そしてテーブル化にはテーブル関数もカーソルも必要がない。
INSERT INTO #TABLE
SELECT
x.c.value('(./CustomerID/text())[1]','varchar(max)') CustomerID,
x.c.value('(./TradeID/text())[1]','varchar(max)') TradeID,
x.c.value('(./Date/text())[1]','varchar(max)') Date,
x.c.value('(./ProdCD/text())[1]','varchar(max)') ProdCD,
x.c.value('(./Amount/text())[1]','varchar(max)') Amount
FROM @XML.nodes('/Parameters') AS x(c)
SQL Server 2005限定とは言え、これは悪くない方法だと思う。
・ストレージエンジン編
・クエリチューニング＆最適化編
・T-SQLクエリ編
・T-SQLプログラミング編
しかし、邦訳が出版されたのは上の２冊だけで、あとの２冊は結局邦訳されなかった。
そこで「T-SQLプログラミング編」を原書で購入して読んでいた。やはりすごい本だ。
この中で、「ストアドプロシージャのパラメータをXMLで受け渡すこと」について言及されていてハッとした。
SQL Server2008ではテーブル値パラメータがサポートされたが、それまではストアドプロシージャにはスカラー形式でのパラメータしか渡すことができなかった。実際にフロントエンドにAccessや.NETのグリッドなどを使っていると、複数セットのデータをパラメータとして受け渡したいことがよく出てくる。
ワンセットであれば、以下のようなパラメータでもいい。
EXEC sp_UpdateInfo @CustomerID,@TradeID,@Date,@ProdCD,@Amount
しかし、パラメータの数が多かったり、複数セットの場合はバラバラのパラメータを用意していてはキリがないので、パラメータは１つにしてデリミタで区切って渡したりする。
EXEC sp_UpdateInfo @PARM
この@PARMに「12345678,12,2008/12/31,101,25!222345678,12,2009/01/04,102,20」などという値をセットすることになるわけだ。
一方、ストアドプロシージャ側でもこれを区切ってテーブルに戻すために、「デリミタ区切りの文字列をテーブル化する」ようなテーブル関数を予め準備しておく必要がある。仮にこれをsf_TOTABLEとすると、テーブル化するのには意外と手間がかかる。
DECLARE D_CUR CURSOR LOCAL FOR
SELECT ITEM FROM sf_TOTABLE(@PARM,'!')
ORDER BY SEQ
OPEN D_CUR
FETCH NEXT FROM D_CUR INTO @ITEM
BEGIN
INSERT INTO #TABLE
SELECT
MAX(CASE WHEN SEQ=1 THEN ITEM END) CustomerID,
MAX(CASE WHEN SEQ=2 THEN ITEM END) TradeID,
MAX(CASE WHEN SEQ=4 THEN ITEM END) ProdCD,
MAX(CASE WHEN SEQ=5 THEN ITEM END) Amount
FROM sf_TOTABLE(@ITEM,',')
FETCH NEXT FROM D_CUR INTO @ITEM
END
CLOSE D_CUR
DEALLOCATE D_CUR
実際にこの方法で受け渡しを行ったりしているのだが、やはりスマートとはいい難い。二重のデリミタに加えて、「何カラム名が何の項目か」も分からない状態で受け渡しをすることになる。
では、XMLをパラメータにするとどうなるか。
パラメータの内容は以下の形になるだろう。
＜Parameters＞
＜CustomerID＞12345678＜/CustomerID＞
＜TradeID＞12＜/TradeID＞
＜Date＞2008/12/31＜/Date＞
＜ProdCD＞101＜/ProdCD＞
＜Amount＞25＜/Amount＞
＜/Parameters＞
＜Parameters＞
＜CustomerID＞22345678＜/CustomerID＞
＜TradeID＞13＜/TradeID＞
＜Date＞2009/01/04＜/Date＞
＜ProdCD＞102＜/ProdCD＞
＜Amount＞20＜/Amount＞
＜/Parameters＞
EXEC sp_UpdateInfo @PARM
受け取ったストアドプロシージャ側では@PARMという１つのパラメータを受け取るにも関わらず、それぞれの項目が何かも把握できる。
そしてテーブル化にはテーブル関数もカーソルも必要がない。
INSERT INTO #TABLE
SELECT
x.c.value('(./CustomerID/text())[1]','varchar(max)') CustomerID,
x.c.value('(./TradeID/text())[1]','varchar(max)') TradeID,
x.c.value('(./Date/text())[1]','varchar(max)') Date,
x.c.value('(./ProdCD/text())[1]','varchar(max)') ProdCD,
x.c.value('(./Amount/text())[1]','varchar(max)') Amount
FROM @XML.nodes('/Parameters') AS x(c)
SQL Server 2005限定とは言え、これは悪くない方法だと思う。
お気に入りの記事を「いいね！」で応援しよう
[DB-TransactSQL] カテゴリの最新記事
-
sp_spaceused 2011.03.21
-
Oracleの機能をSQL Serverで実装する～DML… 2010.05.23
-
ドメインエラーが発生しました 2009.01.09
【毎日開催】
15記事にいいね！で1ポイント
© Rakuten Group, Inc.
