Exécuter une chaîne de caractère contenant une variable T-SQL écrite en dur avec SQL Server




Comment exécuter une chaîne de caractères ayant une variable écrite en dur en T-SQL avec EXEC ? Si vous désirez lancer l’exécution d’une chaine du type : @SQL = ‘SELECT CHAMPS1, CHAMPS2 FROM TABLE_1 WHERE CHAMPS1 = @VARIABLE’ EXEC @SQL et que vous voyez s’afficher le message d’erreur suivant : Must declare the scalar variable « @… » La solution pour éviter ce problème est d’utiliser la procédure T-SQL EXEC sp_executesql. Nous allons voir ensemble un exemple où l’on exécutera une chaîne T-SQL avec la commande spéciale suivante : EXEC sp_executesql

 

Comment exécuter une chaîne de caractère contenant une variable en dur et éviter le message d’erreur : « Must declare the scalar variable « @ » ?

Créer la table destinée à contenir les données de l’exemple.

-- Si la table CLIENTS existe déjà, alors on la supprime
IF exists(   SELECT   1 FROM sys.objects
WHERE   object_id = object_id(N'[dbo].[CLIENTS]') AND type in (N'U')
)
BEGIN DROP TABLE [dbo].[CLIENTS]
END
GO
-- Création de la table CLIENTS avec la colonne NOM déclarée comme UNIQUE
-- Le mot clef UNIQUE définit la colonne avec une valeur unique
-- Une insertion de deux clients avec le même nom est donc impossible
CREATE TABLE [dbo].[CLIENTS] (
  [NOCLIENT]  int IDENTITY(1,1),
  [NOM]     nvarchar(20) UNIQUE,
  [VILLE]   nvarchar(20)
)
GO

 

Notre solution en T-SQL est construite en plusieurs étapes, au besoin les inclure dans une procédure stockée.

  1. Déclaration des variables utilisées, comme le compteur pour la boucle, les variables chaines de caractère, des paramètres, etc.
  2. La construction de la boucle pour chacune des valeurs du compteur.
  3. L’exécution de la requête INSERT dans la boucle
  4. L’affichage de la valeur du compteur

 

-- Déclarer les variables dont on se sert lors de la procédure.
DECLARE @COMPTEUR INT;					--  Le compteur
DECLARE @VARIABLE_NOM NVARCHAR(20);		--  La variable pour le nom du client
DECLARE @VARIABLE_VILLE NVARCHAR(20);	--  La variable pour le nom de la ville
DECLARE @PARAMETRE NVARCHAR(100);		--  La chaîne de paramétrage sert à déclarer les variables
DECLARE @SQL NVARCHAR(100);				--  La chaîne SQL est dans le cas présent une requête d'insertion des lignes

-- La déclaration de notre chaîne dans la variable @PARAMETRE
SET @PARAMETRE = '@VARIABLE_NOM NVARCHAR(20),@VARIABLE_VILLE NVARCHAR(20)';

-- Construit d’une requête basique d'Insertion en utilisant un membre dynamique
SET @SQL = 'INSERT INTO [dbo].[CLIENTS] VALUES (@VARIABLE_NOM,@VARIABLE_VILLE)';

-- Début de la boucle
SET @COMPTEUR = 0 
WHILE @COMPTEUR < 10
  BEGIN
  SET @COMPTEUR = @COMPTEUR + 1
  SET @VARIABLE_NOM = 'NOM-' + CAST(@COMPTEUR AS CHAR);
  SET @VARIABLE_VILLE = 'VILLE-' + CAST(@COMPTEUR AS CHAR);
  -- L’exécution de la requête et une déclaration de la variable puis passage de la valeur depuis @VARIABLE_CHAR
  EXEC	sp_executesql @SQL,@PARAMETRE, @VARIABLE_NOM,@VARIABLE_VILLE;
  PRINT	'LE COMPTEUR VAUT : ' + CAST(@COMPTEUR AS CHAR)
END
-- Fin de la boucle

 

 

Enfin l’affichage des résultats de l’insertion des données.

SELECT	*
FROM	[dbo].[CLIENTS];

Autres avantages de la fonction EXEC sp_executesql

Je vous conseil de vous servir d’EXEC sp_executesql et non de EXECUTE. En effet, cette procédure génère des plans d’exécutions réutilisés pendant les exécutions qui se font en boucle. De cette manière, les performances sont améliorées. Utiliser cette fonction se révèle très utile pour une requête assez complexe exécutée un nombre assez conséquent de fois.