Chaque développeur confirmé ou DBA connaît la définition et l’ usage du SQL Dynamique mais une personne qui découvre les bases de données ne saura pas nécessairement ce que signifie ce terme surtout que l’ aide en ligne de SQL Server ne fournit pas de définition du concept et que très peu d’ auteurs ont trouvé utile de préciser ce concept, spécifiquement pour SQL Server, sur le web.

1 Définition.

Le SQL Dynamique permet de construire une requête de toute pièce dans une chaîne de caractères puis d’ exécuter la chaîne avec la commande EXEC, aussi nommée EXECUTE.

Exemple:

/* Déclaration des variables */
DECLARE @MaColonne AS VARCHAR(25);
DECLARE @MonSQL AS VARCHAR(500);
/* Définition du paramètre */
SET @MaColonne = ‘Nom';
/* Ecriture de la requête */
SET @MonSQL = ‘SELECT ‘ + @MaColonne + ‘ FROM Agriculteur';
/* Exécution de la requête */
EXEC(@MonSQL)

 2 Usage.

L’ usage le plus fréquent est un site de commerce électronique qui propose de sélectionner des articles suivant plusieurs critères différents ( date, auteur, édition, langue ).

Dans la procédure stockée, on doit construire la chaîne de caractères en s’ appliquant à ajouter dans le WHERE les différentes conditions qui ont été choisis par l’ internaute. Une fois, la chaîne de caractères construite. On peux l’ exécuter et ramener les livres sélectionnées.

3 Approfondissements.

Les problèmes que soulève le SQL Dynamique sont le fait que le EXECUTE aura un environnement d’ exécution différent de la procédure stockée qui l’ appelle. Cela entraîne différents problèmes, notamment, si l’ on désire ramener un paramètre depuis EXECUTE.

Pour résoudre le problème des paramètres de sorties, on utilise la procédure stockée système SP_EXECUTESQL.

3.1 SP_EXECUTESQL.

Le premier avantage de SP_EXECUTESQL est que le plan d’ exécution de la requête est conservé par SQL Server entre les différentes exécutions. Pour un lot d’ une centaine d’ exécution, cela peut générer un gain de temps appréciable par rapport à l’ utilisation de EXEC.

Le plus grand avantage de cette fonction est l’ utilisation de paramètres de sorties.

Exemple :

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N’SELECT @max_titleOUT = max(Title)
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N’@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;