Я не раз сталкивался с необходимостью построения динамического запроса и здесь есть ряд подводных камней о которых я расскажу ниже. Пример динамического запроса:
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 ) ссылка на оригинал статьи http://habrahabr.ru/post/270079/
Добавить комментарий