ストアド プロシージャ、EXECUTE AS、文字列変数：動的 SQL 結果の格納方法徹底比較

SQL Server で動的 SQL の結果を変数に格納する

方法 1: sp_executesql を使用する

sp_executesql は、ストアド プロシージャ内で動的 SQL を実行するために使用できるストアド プロシージャです。 結果セットを @output パラメータに格納することができます。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000);

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = @CustomerID';
SET @CustomerID = 123;

EXEC sp_executesql @sql,
                   N'@CustomerID INT',
                   @CustomerID,
                   @output OUTPUT;

SELECT @output;

方法 2: EXECUTE AS を使用する

EXECUTE AS は、現在のユーザーの権限ではなく、指定されたユーザーの権限で SQL ステートメントを実行するために使用できます。 この方法は、結果セットを変数に格納するために使用することもできます。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000);

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = @CustomerID';
SET @CustomerID = 123;

DECLARE @exec_user NVARCHAR(50) = 'sa';

EXECUTE AS @exec_user
BEGIN
    EXEC sp_executesql @sql,
                       N'@CustomerID INT',
                       @CustomerID,
                       @output OUTPUT;
END;

SELECT @output;

方法 3: 文字列変数を使用して SQL を構築する

SQL ステートメントを文字列変数に構築し、その後 EXEC sp_executesql または EXECUTE AS を使用して実行することもできます。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000),
        @CustomerID INT = 123;

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = ' + CONVERT(NVARCHAR(10), @CustomerID);

EXEC sp_executesql @sql,
                   N'@CustomerID INT',
                   @CustomerID,
                   @output OUTPUT;

SELECT @output;

どの方法を使用するべきですか？

使用する方法は、要件によって異なります。

• sp_executesql は、ストアド プロシージャ内で動的 SQL を実行する場合に最もよく使用されます。
• EXECUTE AS は、現在のユーザーの権限ではアクセスできないデータにアクセスする必要がある場合に使用されます。
• 文字列変数を使用して SQL を構築することは、最も柔軟な方法ですが、最もエラーが発生しやすい方法でもあります。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000),
        @CustomerID INT = 123;

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = @CustomerID';

EXEC sp_executesql @sql,
                   N'@CustomerID INT',
                   @CustomerID,
                   @output OUTPUT;

SELECT @output;

このコードは以下の通り動作します。

1. @sql 変数に、Customers テーブルから顧客 ID 123 のレコードを取得する SQL ステートメントが格納されます。
2. @CustomerID 変数の値が sp_executesql プロシージャに渡されます。
3. sp_executesql プロシージャが @sql 変数に格納された SQL ステートメントを実行し、結果を @output 変数に格納します。
4. @output 変数の内容が選択されて、結果セットが表示されます。

このコードを次のように変更して、他のシナリオで使用することもできます。

• 異なるテーブルからレコードを取得するには、SELECT ステートメントを変更します。
• 複数の条件でレコードをフィルターするには、WHERE 句を変更します。
• 結果セットの特定の列のみを取得するには、SELECT ステートメントを変更します。

OPENQUERY は、別のサーバー上のリモート データベースに対してクエリを実行するために使用できる組み込み関数です。 結果セットをローカル変数に格納することができます。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000),
        @serverName NVARCHAR(50) = 'REMOTE_SERVER',
        @databaseName NVARCHAR(50) = 'REMOTE_DB';

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = 123';

SELECT *
INTO @output
FROM OPENQUERY(@serverName, @databaseName, @sql);

方法 5: SQL Server Data Tools を使用する

SQL Server Data Tools (SSDT) は、SQL Server 用の統合開発環境 (IDE) です。 SSDT を使用して、動的 SQL を実行し、結果をデータグリッドなどのコントロールにバインドすることができます。

1. SSDT で新しいプロジェクトを作成し、データベースに接続します。
2. 新しい T-SQL スクリプトを作成します。
3. 以下のコードをスクリプトに貼り付けます。

DECLARE @sql NVARCHAR(4000),
        @output NVARCHAR(4000),
        @CustomerID INT = 123;

SET @sql = N'SELECT * FROM Customers WHERE CustomerID = @CustomerID';

EXEC sp_executesql @sql,
                   N'@CustomerID INT',
                   @CustomerID,
                   @output OUTPUT;

SELECT @output;

1. スクリプトを実行します。
2. 結果がデータグリッドに表示されます。

• OPENQUERY は、リモート データベースのデータにアクセスする必要がある場合に使用されます。
• SSDT は、視覚的に動的 SQL を開発およびテストする場合に最も適しています。