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

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

Комментарии (7)


  1. BelAnt
    03.11.2015 15:52
    #8639921
    +1

    Во всех ваших примерах динамический T-SQL вообще не нужен. Для выполнения запросов с разными параметрами сгодится обычная хранимая процедура.


    1. GrigSV
      03.11.2015 16:32
      #8640091

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


  1. isxaker
    03.11.2015 17:01
    #8640257

    можно добавить про использование uniqueidentifier в dynamic sql 

    CAST(NEWID() AS NVARCHAR(36))


  1. BelAnt
    03.11.2015 17:09
    #8640293
    +9

    В любом случае, для написания параметризированных динамических запросов лучше использовать sp_executesql, а не execute. Тогда вы сможете явно указать типы переменных и защититесь от SQL-инъекций. У вас пропадут проблемы со строками, датами, числами и их округлением + кеширование заработает.

    declare @i int 
declare @paramDefinition nvarchar(500) = '@num int';
declare @sql nvarchar(100) = 'select @num'

EXECUTE sp_executesql @sql, @paramDefinition, @num = @i -- NULL не приводит к ошибке


    1. BelAnt
      03.11.2015 17:21
      #8640325
      +1

      Правильный код для п.5 (про кавычки в строках)

      declare @s varchar(100) = 'Number ''1'' '
declare @paramDefinition nvarchar(500) = '@str varchar(100)';
declare @sql nvarchar(100) = 'select String = @str'

EXECUTE sp_executesql @sql, @paramDefinition, @str = @s


      1. Klajnor
        03.11.2015 18:57
        #8640619

        Вот sp_executesql — это правильно
        Иначе как раз и будут проблемы и со строками и т.д.

        А для простых примеров зачастую и не нужно никаких динамических запросов. Только в случае когда сложный отбор по большому кол-ву условий, которые могут быть заданы, а могут быть и не заданы


    1. GrigSV
      03.11.2015 18:56
      #8640617

      Век живи, век учись. )) спасибо.