LLI Wiki — My Digital Knowledge Base

Dynamic SQL in T-SQL with sp_executesql

Dynamic SQL patterns: parameters, output values, procedure calls, cursors and WITH RESULT SETS.

Patterns for building and running dynamic SQL in T-SQL with sp_executesql, including parameters, output parameters and result sets.

Concatenation versus parameters

Concatenating values into the statement is fragile (quotes) and open to SQL injection. Prefer sp_executesql with a parameter definition.

-- concatenation (avoid)
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

-- parameterised
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

Output parameter

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

Getting a row count from a dynamic table name:

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

Calling a stored procedure dynamically

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

Capturing the result of dynamic SQL into a table

DECLARE @execstr nvarchar(255)
SET @execstr = 'SELECT debug FROM rawdata.[debug]'
INSERT rawdata.[debug]
EXEC sp_executesql @execstr

Creating an object in another database

USE only lasts for the batch, so nest the statements in a string executed with 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);

Cursor over a result set

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

Stored procedure result with a typed result set

exec dbo.GetOrders with result sets ((OrderNr int, PartCode varchar(10), Qty numeric(38,2), Status varchar(20)))

Error handling

begin try
    raiserror('my error', 16, 1)
end try
begin catch
    print 'Error'
end catch

Notes

Mastodon LLIMastodon SocialFosstodon