Dans SQL Server, la procédure stockée système sp_executesql est un outil puissant pour exécuter des requêtes SQL statiques et dynamiques. Il vous permet d’exécuter des instructions SQL stockées dans une chaîne, ce qui le rend flexible et polyvalent. Dans cet article, nous explorerons comment utiliser sp_executesql et discuterons des différents scénarios où il peut être bénéfique.

Exécution de requêtes SQL statiques

Lorsqu’il s’agit d’exécuter des requêtes SQL statiques, sp_executesql peut être utilisé, mais il ne fournit pas d’avantages significatifs par rapport à d’autres méthodes. Vous pouvez passer l’instruction SQL directement en tant que chaîne Unicode ou la stocker dans une variable de type NVARCHAR. Voici un exemple :

EXEC sp_executesql N'SELECT [BusinessEntityID], [Title], [FirstName], [MiddleName], [LastName] FROM [AdventureWorks2017].[Person].[Person] WHERE [PersonType] = ''EM'''

Alternativement, vous pouvez stocker la requête dans une variable et la passer en tant que paramètre :

DECLARE @SQL NVARCHAR(4000) = N'SELECT [BusinessEntityID], [Title], [FirstName], [MiddleName], [LastName] FROM [AdventureWorks2017].[Person].[Person] WHERE [PersonType] = ''EM''';
EXEC sp_executesql @SQL;

Exécution de requêtes SQL dynamiques

La véritable puissance de sp_executesql réside dans sa capacité à exécuter des requêtes SQL dynamiques. Les requêtes SQL dynamiques sont construites à l’exécution en fonction des valeurs des variables. Cela permet une plus grande flexibilité dans la construction de la requête. Regardons un exemple :

DECLARE @cols NVARCHAR(4000) = '';
DECLARE @SQL NVARCHAR(4000) = '';
SELECT @cols = STRING_AGG(COLUMN_NAME, ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' AND TABLE_NAME = 'Person';
SELECT @SQL = 'SELECT ' + @cols + ' FROM Person.Person WHERE PersonType = ''EM''';
EXEC sp_executesql @SQL;

Dans cet exemple, nous construisons dynamiquement une requête pour sélectionner uniquement les colonnes NVARCHAR de la table Person. Les noms des colonnes sont concaténés à l’aide de la fonction STRING_AGG. Cela nous permet de construire une requête qui s’adapte aux besoins spécifiques à l’exécution.

Travailler avec des paramètres

sp_executesql vous permet également de travailler avec des paramètres, à la fois pour les valeurs d’entrée et les valeurs de sortie. Explorons ces scénarios :

Passer des paramètres d’entrée

Si vous avez besoin de réutiliser une requête avec différentes valeurs d’entrée, vous pouvez utiliser des paramètres pour rendre la requête plus flexible. Voici un exemple :

DECLARE @cols NVARCHAR(4000) = '';
DECLARE @SQL NVARCHAR(4000) = '';
DECLARE @params NVARCHAR(4000) = '@Lastname NVARCHAR(255), @FirstName NVARCHAR(255)';
SELECT @cols = STRING_AGG(COLUMN_NAME, ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_SCHEMA = 'Person' AND TABLE_NAME = 'Person';
SELECT @SQL = 'SELECT ' + @cols + ' FROM Person.Person WHERE PersonType = ''EM'' AND LastName = @LastName AND FirstName = @FirstName';
EXEC sp_executesql @SQL, @params, @FirstName = 'Jossef', @Lastname = 'Goldberg';

Dans cet exemple, nous filtrons les résultats en fonction des paramètres d’entrée @FirstName et @Lastname. En utilisant des paramètres, nous pouvons facilement modifier la requête sans changer l’intégralité de l’instruction SQL.

Stockage des paramètres de sortie

Une autre fonctionnalité utile de sp_executesql est la possibilité de stocker les valeurs de sortie dans des paramètres. Cela peut être pratique lorsque vous avez besoin de récupérer des informations spécifiques à partir d’une requête. Voici un exemple :

DECLARE @Tablename NVARCHAR(255) = 'Person.Person';
DECLARE @outCount BIGINT;
DECLARE @params NVARCHAR(255) = '@Count BIGINT OUTPUT';
DECLARE @SQL NVARCHAR(4000) = 'SELECT @Count = COUNT(*) FROM ' + @Tablename;
EXEC sp_executeSQL @SQL, @params, @Count = @outCount OUTPUT;
PRINT (@outCount);

Dans cet exemple, nous récupérons le nombre de lignes d’une table spécifique et le stockons dans la variable @outCount. En utilisant le mot-clé OUTPUT dans la définition du paramètre, nous pouvons facilement capturer le résultat de la requête.

Conclusion

La procédure stockée système sp_executesql dans SQL Server est un outil puissant pour exécuter des requêtes SQL statiques et dynamiques. Il offre une flexibilité et une polyvalence dans la construction de requêtes, permettant la paramétrisation et la récupération de valeurs de sortie. En comprenant comment utiliser sp_executesql, vous pouvez améliorer vos compétences en SQL Server et améliorer l’efficacité de vos opérations de base de données.