Wie kann ich Tabellenvariablen in dynamischen SQL-Anweisungen in SQL Server 2008 ordnungsgemäß verwenden?

Dynamische SQL- und Tabellenvariablen in SQL Server 2008: Eine Lösung

SQL Server 2008 stellt die Verwendung von Tabellenvariablen in dynamischem SQL vor Herausforderungen. Dieser Artikel befasst sich mit diesem Problem und bietet eine praktische Problemumgehung.

Das Problem: Nicht deklarierte Tabellenvariablen

Ein häufiges Szenario besteht darin, dass eine gespeicherte Prozedur versucht, eine dynamische SQL-Abfrage auszuführen, die auf lokal deklarierte Tabellenvariablen verweist. Das Ergebnis ist häufig ein Fehler, der darauf hinweist, dass diese Variablen im dynamischen SQL-Kontext nicht deklariert sind. Betrachten Sie dieses Beispiel:

<code class="language-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>

Dieser Code schlägt mit folgendem Fehler fehl:

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

Die Lösung: Tabellenwertige Parameter

SQL Server 2008 und spätere Versionen bieten eine robuste Lösung: Tabellenwertparameter. Während diese Parameter eine direkte Änderung der Tabellendaten innerhalb des dynamischen SQL verhindern, ermöglichen sie die Referenzierung des Tabelleninhalts.

Um den obigen Code anzupassen, erstellen wir einen benutzerdefinierten Tabellentyp und verwenden ihn dann als Parameter:

<code class="language-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;</code>

Dieser überarbeitete Ansatz verwendet sp_executesql korrekt und übergibt den Tabellenwertparameter @TSku und die Tabellenvariable @RelPro (innerhalb der Prozedur deklariert) als Parameter. Das Schlüsselwort READONLY verhindert eine Änderung der Eingabetabelle. Denken Sie daran, @RelPro innerhalb der gespeicherten Prozedur zu deklarieren. Diese Technik ermöglicht die sichere und effektive Verwendung von Tabellenvariablen in dynamischem SQL.

Das obige ist der detaillierte Inhalt vonWie kann ich Tabellenvariablen in dynamischen SQL-Anweisungen in SQL Server 2008 ordnungsgemäß verwenden?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!