SQL dynamique en T-SQL avec sp_executesql
Modèles de SQL dynamique : paramètres, sortie, appel de procédure, curseur et WITH RESULT SETS.
Modèles pour construire et exécuter du SQL dynamique en T-SQL avec sp_executesql : paramètres, paramètres de sortie et jeux de résultats.
Concaténation ou paramètres
Concaténer les valeurs dans la requête est fragile (apostrophes) et expose à l'injection SQL. Préférer sp_executesql avec une définition de paramètres.
-- concaténation (à éviter)
declare @typeClient varchar(50) = 'N05'
declare @sql nvarchar(max)
set @sql = 'SELECT [Name],[Type],[Path] FROM [AppDb].[dbo].[Desktop] where TypeClient = ''' + @typeClient + ''''
exec sp_executesql @sql
-- paramétrée
declare @sqlvars nvarchar(max) = N'@_typeClient varchar(50)'
set @sql = 'SELECT [Name],[Type],[Path] FROM [AppDb].[dbo].[Desktop] where TypeClient = @_typeClient'
exec sp_executesql @sql, @sqlvars, @_typeClient = @typeClient
Paramètre de sortie
declare @typeClient varchar(50) = 'N05'
declare @return varchar(50)
declare @sql nvarchar(max)
declare @sqlvars nvarchar(max) = N'@_typeClient varchar(50), @_return varchar(50) output'
set @sql = 'SELECT [Name],[Type],[Path] FROM [AppDb].[dbo].[Desktop] where TypeClient = @_typeClient;
select @_return = ''value'''
exec sp_executesql @sql, @sqlvars, @_typeClient = @typeClient, @_return = @return output
print @return
Obtenir un nombre de lignes à partir d'un nom de table dynamique :
declare @sqlcount nvarchar(max), @sqlvars nvarchar(max), @res int
set @sqlcount = N'select @res = count(*) from dbo.' + quotename(@table) + @strWhere
set @sqlvars = N'@res int OUTPUT'
exec sp_executesql @sqlcount, @sqlvars, @res OUTPUT
print @res
Appeler dynamiquement une procédure stockée
declare @sqlsect nvarchar(4000), @sqlvars nvarchar(4000)
set @sqlsect = 'exec dbo.UpdateAfterImport @Year, @Code'
set @sqlvars = N'@Year varchar(4), @Code varchar(20)'
exec sp_executesql @sqlsect, @sqlvars, @Year = @year_value, @Code = @code_value
Récupérer le résultat d'un SQL dynamique dans une table
DECLARE @execstr nvarchar(255)
SET @execstr = 'SELECT debug FROM rawdata.[debug]'
INSERT rawdata.[debug]
EXEC sp_executesql @execstr
Créer un objet dans une autre base
USE ne vaut que pour le lot en cours : on imbrique donc les instructions dans une chaîne exécutée avec EXEC.
USE master; DECLARE @SQL VARCHAR(max); SET @SQL = 'USE tempdb; DECLARE @SQL VARCHAR(max);SET @SQL = ''CREATE PROCEDURE dbo.P_MY_PROC AS SELECT * FROM INFORMATION_SCHEMA.ROUTINES;''EXEC (@SQL);'; EXEC (@SQL);
Curseur sur un jeu de résultats
declare @Name varchar(50), @Type varchar(50), @Path varchar(max)
declare cur_desktop cursor for
SELECT [Name],[Type],[Path] FROM [AppDb].[dbo].[Desktop] where TypeClient = 'N05'
open cur_desktop
fetch cur_desktop into @Name, @Type, @Path
while (@@FETCH_STATUS = 0)
begin
print @Path
fetch cur_desktop into @Name, @Type, @Path
end
close cur_desktop
deallocate cur_desktop
Résultat d'une procédure avec jeu de résultats typé
exec dbo.GetOrders with result sets ((OrderNr int, PartCode varchar(10), Qty numeric(38,2), Status varchar(20)))
Gestion d'erreurs
begin try
raiserror('my error', 16, 1)
end try
begin catch
print 'Error'
end catch
Remarques
- Sous SQL Server 2005, l'appel d'une procédure via une variable exigeait
exec; ce n'est plus nécessaire depuis 2008. - Utiliser
quotename()sur les noms d'objets construits dynamiquement.