Comment puis-je utiliser correctement les variables de table dans les instructions SQL dynamiques dans SQL Server 2008 ?

SQL dynamique et variables de table dans SQL Server 2008 : une solution

SQL Server 2008 présente des défis lors de l'utilisation de variables de table dans du SQL dynamique. Cet article aborde ce problème et propose une solution de contournement pratique.

Le problème : les variables de table non déclarées

Un scénario courant implique une procédure stockée tentant d'exécuter une requête SQL dynamique faisant référence à des variables de table déclarées localement. Le résultat est souvent une erreur indiquant que ces variables ne sont pas déclarées dans le contexte SQL dynamique. Prenons cet exemple :

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

Ce code échouera avec l'erreur :

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

La solution : les paramètres à valeur de table

SQL Server 2008 et les versions ultérieures offrent une solution robuste : les paramètres table. Bien que ces paramètres empêchent la modification directe des données de la table dans le SQL dynamique, ils permettent de référencer le contenu de la table.

Pour adapter le code ci-dessus, nous allons créer un type de table défini par l'utilisateur puis l'utiliser comme paramètre :

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

Cette approche révisée utilise sp_executesql correctement, en passant le paramètre table @TSku et la variable table @RelPro (déclarée dans la procédure) comme paramètres. Le mot-clé READONLY empêche la modification de la table d'entrée. Pensez à déclarer @RelPro à l'intérieur de la procédure stockée. Cette technique permet une utilisation sûre et efficace des variables de table dans SQL dynamique.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!