Procédure T-SQL avec une variable en dur et du code dynamique

Comment exécuter une procédure T-SQL avec une variable stockée en dur ?

Tutoriel pour gérer l’exécution d’une procédure T-SQL avec une variable en dur, c’est à dire dans une chaîne de charactère. En effet, une exécution normale ne fonctionne pas avec la commande EXEC seule. Alors comment contourner cette erreur ? La solution au problème est d’utiliser la fonction T-SQL EXEC sp_executesql.

1. Exécuter une procédure T-SQL avec une variable 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. Le but est de construire et d’exécuter une requête dynamique de ce 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« .

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

2. Eviter l’erreur SQL Server Must declare the scalar variable

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.

2..1 Construire le code T-SQL dynamique étape par étape

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

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

SELECT	*
FROM	[dbo].[CLIENTS];

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

Gérer les champs textes SQL Server de plus de 8000 caractères