LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon