Lorsqu'il est nécessaire de déterminer la requête SQL à exécuter en fonction de paramètres externes, la construction dynamique de requêtes devient essentielle. Cette approche est couramment appliquée dans les procédures stockées de pagination ou pour la construction de requêtes de recherche. Une procédure de pagination générique peut nécessiter des paramètres tels que le nom de la table, les colonnes, les filtres et le tri. Pour la recherche, la requête SQL finale est souvent construite de manière dynamique selon les critères fournis.

Dans SQL Server, deux méthodes permettent d'exécuter des requêtes SQL dynamiques : exec et sp_executesql. La procédure sp_executesql offre des avantages significatifs. Elle fournit une interface d'entrée/sortie, permettant de passer directement des variables dans la requête SQL, alors que exec repose uniquement sur la concaténation. De plus, sp_executesql permet la réutilisation des plans d'exécution, ce qui améliore considérablement les performances. Il est donc généralement recommandé d'utiliser sp_executesql pour exécuter des requêtes dynamiques.

Un point crucial lors de l'utilisation de sp_executesql est que la requête SQL fournie doit être une chaîne au formatt Unicode. Ainsi, la variable stockant la requête doit être déclarée avec le type nvachar (ou nvachar(max) si la longueur est inconnue). Sans cela, une erreur de type « le paramètre '@statement' requiert un type 'ntext/nchar/nvachar' » sera levée. Si vous exécutez la requête directement avec sp_executesql, préfixez-la du préfixe Unicode N.

Examinons plusieurs scénarios d'exécution dynamique.

1. Requête SQL simple

EXEC('SELECT * FROM Etudiants');

-- Nécessite le préfixe N pour sp_executesql
EXEC sp_executesql N'SELECT * FROM Etudiants';

2. Requête SQL avec paramètres

Méthode avec exec (concaténation) :

DECLARE @requete NVARCHAR(1000);
DECLARE @idUtilisateur VARCHAR(100);

SET @idUtilisateur = 'U001';
SET @requete = 'SELECT * FROM Etudiants WHERE IdentifiantUtilisateur = ''' + @idUtilisateur + '''';

EXEC(@requete);

Méthode avec sp_executesql (paramétrage) :

DECLARE @requete NVARCHAR(1000);
DECLARE @idUtilisateur VARCHAR(100);

SET @idUtilisateur = 'U001';
SET @requete = N'SELECT * FROM Etudiants WHERE IdentifiantUtilisateur = @idUtilisateur';

EXEC sp_executesql @requete, N'@idUtilisateur VARCHAR(100)', @idUtilisateur;

Cet exemple illustre que sp_executesql intègre directement les paramètres dans la requête, tandis que exec nécessite la concaténation. L'approche paramétrée offre une meilleure sécurité contre les injections SQL. Rappelons que la variable contenant la requête doit être de type nvachar.

3. Requête SQL avec paramètres de sortie

CREATE PROCEDURE [dbo].[ObtenirNomParIdUtilisateur]
    @idUtilisateur VARCHAR(100),
    @nomUtilisateur VARCHAR(100) OUTPUT
AS
BEGIN
    DECLARE @requete NVARCHAR(1000);
    SET @requete = N'SELECT @nomUtilisateur = NomComplet FROM Etudiants WHERE IdentifiantUtilisateur = @idUtilisateur';

    EXEC sp_executesql 
        @requete, 
        N'@idUtilisateur VARCHAR(100), @nomUtilisateur VARCHAR(100) OUTPUT', 
        @idUtilisateur, 
        @nomUtilisateur OUTPUT;

    SELECT @nomUtilisateur;
END