Bagaimanakah Saya Boleh Menggunakan Pembolehubah Jadual dengan Betul dalam Pernyataan SQL Dinamik dalam SQL Server 2008?

SQL Dinamik dan Pembolehubah Jadual dalam SQL Server 2008: Penyelesaian

SQL Server 2008 membentangkan cabaran apabila menggunakan pembolehubah jadual dalam SQL dinamik. Artikel ini menangani isu ini dan menawarkan penyelesaian praktikal.

Masalahnya: Pembolehubah Jadual Tidak Diisytiharkan

Senario biasa melibatkan prosedur tersimpan yang cuba melaksanakan pertanyaan SQL dinamik yang merujuk pembolehubah jadual yang diisytiharkan secara tempatan. Hasilnya selalunya ralat yang menunjukkan bahawa pembolehubah ini tidak diisytiharkan dalam konteks SQL dinamik. Pertimbangkan contoh ini:

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;

Kod ini akan gagal dengan ralat:

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

Penyelesaian: Parameter Bernilai Jadual

SQL Server 2008 dan versi yang lebih baru menyediakan penyelesaian yang mantap: parameter bernilai jadual. Walaupun parameter ini menghalang pengubahsuaian langsung data jadual dalam SQL dinamik, parameter ini membenarkan rujukan kandungan jadual.

Untuk menyesuaikan kod di atas, kami akan mencipta jenis jadual yang ditentukan pengguna dan kemudian menggunakannya sebagai parameter:

-- 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;

Pendekatan yang disemak ini menggunakan sp_executesql dengan betul, menghantar parameter bernilai jadual @TSku dan pembolehubah jadual @RelPro (diisytiharkan dalam prosedur) sebagai parameter. Kata kunci READONLY menghalang pengubahsuaian jadual input. Ingat untuk mengisytiharkan @RelPro di dalam prosedur tersimpan. Teknik ini membolehkan penggunaan pembolehubah jadual yang selamat dan berkesan dalam SQL dinamik.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Menggunakan Pembolehubah Jadual dengan Betul dalam Pernyataan SQL Dinamik dalam SQL Server 2008?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!