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
- In SQL Server 2005, calling a procedure through a variable required
exec; this was no longer needed from 2008. - Use
quotename()on object names built dynamically.