Передача параметров в динамический запрос в T-SQL

Я не раз сталкивался с необходимостью построения динамического запроса и здесь есть ряд подводных камней о которых я расскажу ниже. Пример динамического запроса:

declare @sql varchar(100) = 'select 1+1'
execute( @sql)

1. Запуск строки через Execute создает отдельный блок кода, в котором текущие переменные будут не видны, но видны все временные таблицы.

2. Обратите внимание на передачу переменных со значением NULL. Любое слияние с NULL в результате даст NULL, следовательно, вместо запроса, вы можете получить пустую строку.

declare @i int
declare @sql varchar(100) = 'select ' + cstr(@i)
execute( @sql ) -- Ошибка

3. Передачу дат и времени. Даты лучше передавать в формате ГГГГММДД. При передаче параметров со временем следует обратить внимание на потерю точности. Для сохранения точности значения лучше передавать через временную таблицу.

4. Передача параметров с плавающей десятичной точкой имеет те же проблемы, что и передача времени внутрь построенного запроса.

5. Строковые значения – потенциально опасный код. Для начала внутри строки все одинарные кавычки должны быть продублированы. Сама строка заключена в одинарные кавычки.

Пример ошибочного кода:

Declare @str varchar(100) = 'Number ''1'' '
Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + @str + '''', 'null' )
Execute( @sql ) -- запуск кода выдаст ошибку

Правильный код:

Declare @str varchar(100) = 'Number ''1'' '
Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + replace( @str, '''', '''''')  + '''', 'null' )
Execute( @sql )

6. Подстановка списков в секцию IN. Основная опасность – пустой список. В этом случае секция будет иметь вид типа ‘поле IN ()’, что при компиляции вызовет ошибку. Как метод борьбы: в начало списка всегда включать NULL или заменить пустую строку на NULL. NULL можно сравнивать с любым типом данных. Сравнение с NULL всегда дает отрицательный результат, но при этом список гарантированно не пустой.

Declare @list varchar(100) = ''
iif @list = '' set @list = 'null'
Declare @sql varchar(1000) = 'select number from documents where id in ('+@list+') '
Execute( @sql ) 

Вот пример безопасной передачи сложных параметров через временную таблицу:

if OBJECT_ID('tempdb..#params') is not null drop table #params
create table #params ( v1 int, v2 datetime, v3 varchar(100) )

insert #params values ( 1, getdate(), 'Строка ''1''')
declare @sql varchar(1000) = '
  declare @v1 int, @v2 datetime, @v3 varchar(100)
  select @v1 = v1 , @v2 = v2, @v3 = v3 from #params
  select @v1, @v2, @v3
'
execute(@sql)
drop table #params

Ну и на закуску маленькие хитрости:

Передаваемые параметры лучше вначале объявить через переменные, инициализировать эти переменные и уже эти переменные использовать в ходе вычислений. В этом случае повышается читаемость текста запроса и отлаживать его легче.

Если обходится без переменных, то можно использовать следующий метод:

set @sql = 'select <VAR1> + <VAR2>'
set @sql = replace(@sql, '<VAR1>', '1')
set @sql = replace(@sql, '<VAR2>', '2')
execute( @sql )

Кроме вышеперечисленных особенностей есть еще пару способов передачи параметров:
1. Использовать sp_executesql (как правильно мне подсказали в комментариях)
2. Обернуть запрос во временную хранимую процедуру и запускать ее. При большом количестве запусков этот способ даже эффективнее.

declare @sql varchar(200) = ' create procedure #test ( @p1 int, @p2 int) as select @p1 + @p2'
execute( @sql )

exec #test 1, 2
exec #test 3,4
drop procedure #test