ユーザー定義テーブル型変数を使って、ストアドプロシージャに複数のレコードを一度に渡す

ストアドプロシージャに複数のレコードを一度に渡すには？

一度のストアドプロシージャの実行で、複数のレコードを処理したいような時ありますよね。

カラムがひとつで、値が ID やコードなどの場合は、カンマで区切り文字を生成して文字列として渡し、ストアドプロシージャ内で分解するようなことも可能かと思いますが、複数カラムの場合は複雑になってしまうかもしれません。

今回はユーザー定義テーブル型を作って、それをテーブル型の引数として、複数のレコードを一度にストアドプロシージャに渡す方法をご紹介します。

ユーザー定義のテーブル型を作成する

テーブルの定義の部分は、CREATE TABLE と同じようないろいろなオプションが指定できますが、シンプルなユーザー定義のテーブル型を作成する構文は次の通りです。

CREATE TYPE [ユーザー定義テーブル型の名前] AS TABLE(
    [カラム1] [データ型] [NULL or NOT NULL],
    [カラム2] [データ型] [NULL or NOT NULL],
    [カラム3] [データ型] [NULL or NOT NULL],
    ...
);

例えば、次のような学生の情報を保持している Student テーブルがあり、クライアントプログラム側で、グリッドで学生情報の追加・変更・削除ができるとします。

CREATE TABLE Student (
   StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

INSERT INTO Student
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Taro', 'Yamada', '1980-02-15', 'M'),
  ('Hanako', 'Tanaka' ,'1979-12-30', 'F'),
  ('Yuko', 'Suzuki', '1979-07-07', 'F'),
  ('Takao', 'Sato', '1980-03-12', 'M'),
  ('Hiroki', 'Takagi', '1979-04-05', 'M'),
  ('Yuka', 'Kimura', '1981-03-27', 'F');

クライアントプログラムで編集した後に、データベースに保存する際、1 レコードずつストアドプロシージャで更新した場合、追加と変更は StudentID をキーに判断可能ですが、削除はもう消えてしまっているので、ひと工夫が必要です。

全レコードを一度にストアドプロシージャに渡すと、削除の判断もストアドプロシージャ内で可能で、かつ、データベースとクライアントプログラムとのやりとりの回数を減らすことができるので便利です。

それでは、まず、次のスクリプトを実行して Student テーブルの値を渡すためのユーザー定義のテーブル型を作ります。

CREATE TYPE StudentTableType AS TABLE (
    StudentID INT NOT NULL,
    FirstName VARCHAR(50) NULL,
    LastName VARCHAR(50) NULL,
    Birthday DATE NULL,
    Gender CHAR(1) NULL
);

[ 実行結果 ]

※ カラム名は Student テーブルと同じである必要はありません。共通で使えるユーザー定義テーブル型を作って使いまわしてもかまいません。

ユーザー定義のテーブル型を引数として受け取るストアドプロシージャを作成する

次に StudentTableType を引数として受けとって、Student テーブルを更新するストアドプロシージャを作ります。

StudentID は Identity カラムで、新規レコードには StudentID = 0 がクライアントプログラムで設定されているものとします。

CREATE PROCEDURE uspStudentSave
    @StudentTable dbo.StudentTableType READONLY
AS
BEGIN

    DELETE  S
    FROM    Student AS S
    WHERE   NOT EXISTS
            (SELECT *
             FROM   @StudentTable AS I
             WHERE  I.StudentID = S.StudentID);

    UPDATE  S
    SET     LastName = I.LastName,
            FirstName = I.FirstName,
            Birthday = I.Birthday,
            Gender = I.Gender
    FROM    Student AS S
                INNER JOIN @StudentTable AS I
                    ON S.StudentID = I.StudentID;

    INSERT INTO Student (
                LastName,
                FirstName,
                Birthday,
                Gender
        )
        SELECT  LastName,
                FirstName,
                Birthday,
                Gender
        FROM    @StudentTable AS I
        WHERE   NOT EXISTS
                (SELECT *
                 FROM   Student AS S
                 WHERE  S.StudentID = I.StudentID);

END

[ 実行結果 ]

テーブル型の引数は変更不可で、READONLY のキーワードをつける必要があります。

後は、StudentID をキーに、@StudentTable のデータを使って、 Student テーブルの削除、変更、新規作成を行っています。

ユーザー定義のテーブル型を引数として受け取るストアドプロシージャを実行する

それでは作ったユーザー定義のテーブル型を引数として受け取るストアドプロシージャを実行してみましょう。

DECLARE @StudentTable dbo.StudentTableType;

INSERT INTO @StudentTable
  (StudentID, FirstName, LastName, Birthday, Gender)
 VALUES
  (1, 'Taro', 'Yamada', '1980-02-15', 'M'),
  (4, 'Takao', 'Sato', '1980-03-12', 'M'),
  (5, 'Hiroko', 'Takagi', '1979-04-05', 'F'),
  (6, 'Yuka', 'Kimura', '1981-03-27', 'F'),
  (0, 'Ryoko', 'Okamoto', '1981-08-08', 'F');


EXEC uspStudentSave @StudentTable;

[ 実行結果 ]

StudentID = 2 と 3 のレコードが削除され、StudentID = 0　のレコードが追加、 StudentID = 5 の学生の情報が変更されましたね。