sp_executesql で OUTPUT を取得する

SQL Server で動的に作成したスクリプトを実行したい時にはシステムストアードプロシージャの sp_executesql や EXEC (EXECUTE) ステートメント使って実行することができます。

ここでは、sp_executesql を使って、OUTPUT パラメーターの値や、結果セットを取得する方法をご紹介します。

sp_executesql で OUTPUT パラメーターの値を取得する

sp_executesql システムストアードプロシージャで動的に作成したスクリプトを実行した際に、OUTPUT パラメーターを指定して値を取得することができます。

例えば、次のような Student テーブルがあります。

Student テーブルの中の Gender が 'M' のレコード数を sp_executesql を使って OUTPUT パラメーターで取得したい時には、次のようにできます。

DECLARE @Sql NVARCHAR(500),
        @RecordCount INT;

SET @Sql = N'SET @RecordCount = ISNULL((SELECT COUNT(*)
                                        FROM   Student
                                        WHERE  Gender = @Gender), 0);';

EXEC sp_executesql　@Sql,
                  　N'@Gender CHAR(1), @RecordCount INT OUTPUT', 
                  　@Gender = 'M',
                  　@RecordCount = @RecordCount OUTPUT;
 
SELECT @RecordCount AS RecordCount;

このように、パラメーターの定義の箇所と、パラメータの値を指定する箇所に出力パラメータであることを示すために OUTPUT キーワードを追加します。

Gender = 'M' のレコードは 3 つなので、@RecordCount に 3 が入っていますね。

sp_executesql で結果セットを取得する

sp_executesql を使って、Gender = 'M' のレコードの数ではなくて、Gender = 'M' のレコードを結果セットとして取得することもできます。

その際は、先に結果セットが保持できる構造を持つテーブル変数か一時テーブルを作っておく必要があります。例えば次のような感じです。

DECLARE @MaleStudent TABLE (
	StudentID INT,
	FirstName NVARCHAR(50),
	LastName NVARCHAR(50),
	Gender CHAR(1)
);

DECLARE @Sql NVARCHAR(500);

SET @Sql = N'SELECT StudentID,
                    FirstName,
                    LastName,
                    Gender
             FROM   Student
             WHERE  Gender = @Gender;';

INSERT INTO @MaleStudent
  EXEC sp_executesql @Sql,
                     N'@Gender CHAR(1)', 
                     @Gender = 'M';
 
SELECT * FROM @MaleStudent;

「 INSERT INTO テーブル変数（一時テーブル） EXEC sp_executesql ... 」のようにして、結果セットをテーブル変数（一時テーブル）に挿入します。

Student テーブルの Gender = 'M' のレコードが @MaleStudent に取得できていますね。

以上、sp_executesql を使って、OUTPUT パラメーターの値や、結果セットを取得する方法をご紹介しました。