SQL Server 2008 の動的 SQL ステートメントでテーブル変数を適切に使用するにはどうすればよいですか?

SQL Server 2008 の動的 SQL とテーブル変数: ソリューション

SQL Server 2008 では、動的 SQL 内でテーブル変数を使用するときに課題が生じます。この記事ではこの問題に対処し、実用的な回避策を提供します。

問題: 未宣言のテーブル変数

一般的なシナリオには、ローカルに宣言されたテーブル変数を参照する動的 SQL クエリを実行しようとするストアド プロシージャが含まれます。 その結果、多くの場合、これらの変数が動的 SQL コンテキスト内で宣言されていないことを示すエラーが発生します。 次の例を考えてみましょう:

DECLARE @col_name NVARCHAR(MAX), @sqlstat NVARCHAR(MAX), @curr_row INT, @curr_row1 INT;
-- ... other code to populate @curr_row and @curr_row1 ...

DECLARE @RelPro TABLE (RowID INT, Assoc_Item_1 INT, Assoc_Item_2 INT, ...);
DECLARE @TSku TABLE (tid INT, relsku INT);
-- ... populate @RelPro and @TSku ...

SET @col_name = 'Assoc_Item_' + CONVERT(NVARCHAR(2), @curr_row1);

SET @sqlstat = 'UPDATE @RelPro SET ' + @col_name + 
               ' = (SELECT relsku FROM @TSku WHERE tid = ' + 
               CONVERT(NVARCHAR(2), @curr_row1) + ') WHERE RowID = ' + 
               CONVERT(NVARCHAR(2), @curr_row);

EXEC sp_executesql @sqlstat;

このコードは次のエラーで失敗します:

<code>Must declare the table variable "@RelPro".
Must declare the table variable "@TSku".</code>

解決策: テーブル値パラメーター

SQL Server 2008 以降のバージョンでは、テーブル値パラメーターという堅牢なソリューションが提供されます。これらのパラメータは動的 SQL 内でテーブル データを直接変更することを防ぎますが、テーブルの内容を参照することは可能です。

上記のコードを適応させるために、ユーザー定義のテーブル タイプを作成し、それをパラメータとして使用します。

-- Create a user-defined table type
CREATE TYPE MySkuTable AS TABLE (tid INT, relsku INT);
GO

-- Stored Procedure using Table-Valued Parameter
CREATE PROCEDURE UpdateRelPro (@TSku MySkuTable READONLY, @curr_row INT, @curr_row1 INT)
AS
BEGIN
    DECLARE @col_name NVARCHAR(MAX), @sqlstat NVARCHAR(MAX);
    SET @col_name = 'Assoc_Item_' + CONVERT(NVARCHAR(2), @curr_row1);
    SET @sqlstat = N'UPDATE @RelPro SET ' + @col_name + 
                   N' = (SELECT relsku FROM @TSku WHERE tid = ' + 
                   CONVERT(NVARCHAR(2), @curr_row1) + N') WHERE RowID = ' + 
                   CONVERT(NVARCHAR(2), @curr_row);

    -- Declare @RelPro within the procedure
    DECLARE @RelPro TABLE (RowID INT, Assoc_Item_1 INT, Assoc_Item_2 INT, ...);
    -- ... populate @RelPro ...

    EXEC sp_executesql @sqlstat, N'@RelPro MyTable READONLY', @RelPro = @RelPro;
END;
GO

-- Example Usage:
DECLARE @TSku MySkuTable;
INSERT INTO @TSku VALUES (1, 10), (2, 20);

DECLARE @RelPro TABLE (RowID INT, Assoc_Item_1 INT, Assoc_Item_2 INT);
INSERT INTO @RelPro VALUES (1, NULL, NULL);

EXEC UpdateRelPro @TSku, 1, 1;

この改訂されたアプローチでは sp_executesql が正しく使用され、テーブル値パラメーター @TSku とテーブル変数 @RelPro (プロシージャー内で宣言されている) がパラメーターとして渡されます。 READONLY キーワードは入力テーブルの変更を防ぎます。 ストアド プロシージャの @RelPro 内 を忘れずに宣言してください。 この手法により、動的 SQL 内でテーブル変数を安全かつ効果的に使用できるようになります。

以上がSQL Server 2008 の動的 SQL ステートメントでテーブル変数を適切に使用するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。