Exécuter une requête SQL Server avec une variable en dur

Comment exécuter une requête SQL Server avec une variable stockée en dur comme du texte ? Lancer l’exécution d’une chaîne de charactère avec des variables SQL Server ne fonctionne pas avec la commande EXEC seule.

En effet, une requête du type : “@SQL = ‘SELECT CHAMPS1, CHAMPS2 FROM TABLE_1 WHERE CHAMPS1 = @VARIABLE’ EXEC @SQL”.  Le message d’erreur suivant s’affiche : “Must declare the scalar variable “@SQL”.

Alors comment contourner cette erreur ? La solution au problème est d’utiliser la fonction T-SQL EXEC sp_executesql.

Exécuter une requête SQL Server avec un paramètre stocké en dur

En effet, voici un exemple de code pour exécuter une chaîne de caractères en T-SQL avec la commande dans une variable contenant elle-même une variable écrite en dur dans le texte.

Tout d’abord, créer la table des clients pour insérer les données comme dans l’exemple ci-dessous.

-- Tester si la table CLIENTS existe, au besoin alors supprimer la table
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éer la table CLIENTS avec la colonne NOM déclarée comme UNIQUE
-- Le mot clef UNIQUE définit la colonne avec une valeur unique
-- Insérer deux clients avec le même nom est impossible

CREATE TABLE [dbo].[CLIENTS](
  [NOCLIENT]  	int IDENTITY(1,1),
  [NOM]     	nvarchar(20) UNIQUE,
  [VILLE]   	nvarchar(20)
)
GO

Erreur SQL Server : Must declare the scalar variable, l’éviter avec ce script

Voici la solution en T-SQL pour exécuter une requête SQL Server avec une variable. Construire 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, et la variable avec une chaîne de caractères, 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 utilisée dans la procédure stockée.
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

-- Déclarer une chaîne en dur dans la variable @PARAMETRE
SET @PARAMETRE = '@VARIABLE_NOM NVARCHAR(20),@VARIABLE_VILLE NVARCHAR(20)';

-- Construire une requête T-SQL basique d'Insertion avec 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);
  
  -- Exécuter la requête, déclarer la variable et passer de la valeur via @VARIABLE_CHAR
  EXEC	sp_executesql @SQL,@PARAMETRE, @VARIABLE_NOM,@VARIABLE_VILLE;
  
  PRINT	'LE COMPTEUR VAUT : ' + CAST(@COMPTEUR AS CHAR)
END
-- Fin de la boucle
Requête SQL pour exécuter une chaine de caractères avec une variable
Requête SQL dans SSMS pour exécuter une requête avec une variable en SQL

Enfin l’affichage des résultats de l’insertion des données avec une simple requête SELECT SQL Server.

SELECT	*
FROM	[dbo].[CLIENTS];
Résultat de la requête dans SSMS après exécution de la requête contenant des variables en dur
Résultat de la requête dans SSMS après exécution de la requête contenant des variables en dur

Autres avantages de la fonction EXEC sp_executesql

Enfin, utiliser la fonction EXEC sp_executesql à la place de EXECUTE. Cette procédure génère des plans d’exécutions réutilisables pendant les exécutions 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 complexe exécutée un nombre conséquent de fois.

Cette méthode permet de générer du code particulièrement dynamique et donc d’exécuter une requête avec une variable en T-SQL qui est remplacée à la volée pendant l’exécution du code.

Après cet article avec un exemple pour exécuter une chaîne de caractères avec une variable en T-SQL, voici un article pour gérer les champs SQL Server de plus de 8000 caractères. Ou encore un article pour découper une chaîne de caractères avec séparateurs de type point virgule.

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*