Microsoft SQL Server
Transact-SQL

Динамический запрос или "переменная @Tablename"

Опубликовано: 27 сен 02
Рейтинг:

Автор: Glory
Прислал: Glory

Наверное, одним из первых вопросов, возникающих у начинающих программистов на T-SQL, это вопрос "А как получить выборку из таблицы, имя которой занесено в переменную ?"
Т.к. в T-SQL нет возможности использовать в некоторых частях запроса значения переменных, то единственным доступным решением является использование динамического запроса. Идея очень проста: в специально определнной переменной "собирается" строка запроса, которая должна выполняться. Далее эта строка запускается на выполнение. Запуск можно осуществить двумя способами
- с помощью команды EXECUTE
- с помощью системной процедуры sp_executesql.

Выглядит это приблизительно так

DECLARE @SQL varchar(8000), @table_name varchar(10)
SET @SQL = 'SELECT * FROM ' + @table_name

exec(@SQL)
--или

exec sp_executesql @SQL
Обычно динамические запроса формируются внутри хранимых процедур, в которых по входным параметром составляется конкретная строка выполнения.

I.Особенности динамического запроса
1. Динамический запрос ВСЕГДА выполняется В ТОМ-ЖЕ КОННЕКТЕ и КАК ОТДЕЛЬНЫЙ ПАКЕТ(batch). Другими словами при использовании такого запроса,
- вы ни имеете доступа к локальным переменным, объявленым до вызова динамического запроса (однако возможен доступ к cозданным ранее временным таблицам)
- локальные временые таблицы и переменные, созданные во время выполнения команды exec, будут недоступны в вызывающей процедуре, т.к. будут удалены по окончании пакета, в котором выполняется exec.

2. Динамический запрос ВСЕГДА выполняется с ПРАВАМИ ПОЛЬЗОВАТЕЛЯ, ВЫЗВАВШЕГО ПРОЦЕДУРУ, а не с правами владельца процедуры. Другими словами, если владельцем процедуры Procedure1 является User1, который имеет права к таблице Table1, то для пользователя User2 мало назначить права на выполнение процедуры Procedure1, если обращение в ней к таблице Table1 идет через динамический запрос. Придется давать ему соответствующие права и непосредственно для Table1.

3. Компиляция запроса происходят непосредственно перед его вызовом. Т.е. обо всех синтаксических ошибках вы узнаете только в этот момент.


II.Особенности использования команда exec
1. Команда exec поддерживает к качестве аргумента конкатенацю строк и/или переменных. НО не поддерживатеся конкатенация результатов выполнения функций, т.е. конструкции вида
exec('SELECT * FROM ' + LEFT(@TableName, 10))
запрещены к использованию.
2. В команде нет входных/выходных параметров.



III.Особенности использования процедуры sp_executesql
1. Процедура НЕ поддерживает в качестве параметров конкатенацию строк и/или переменных.
2. Текст запроса должен быть либо переменной типа NVARCHAR/NCHAR, либо такого же типа стринговой константой.
3. Имеется возможность передачи параметров в выполняемый скрипт и получение выходных значений
Последнее явно в документации не описано, поэтому вот несколько примеров

В данном примере в динамический запрос передаются 4 переменные, три из которых являюся выходными


declare @var1 int, @var2 varchar(100), @var3 varchar(100), @var4 int
declare @mysql nvarchar(4000)
set @mysql = 'set @var1 = @var1 + @var4; set @var2 = ''CCCC''; set @var3 = @var3 + ''dddd'''
set @var1 = 0
set @var2 = 'BBBB'
set @var3 = 'AAAA'
set @var4 = 10

select @var1, @var2, @var3
exec sp_executesql @mysql, N'@var1 int out, @var2 varchar(100) out, @var3 varchar(100) out, @var4 int',   
@var1 = @var1 out, @var2 = @var2 out, @var3 = @var3 out, @var4 = @var4
select @var1, @var2, @var3



В данном примере в динамическом запросе открывается курсор, который доступен в вызывающей процедуре через выходную переменную


USE pubs
declare @cur cursor
exec sp_executesql N'set @curvar= cursor local for select top 10 au_id, au_lname, au_fname from authors open @curvar' , 
N'@curvar cursor output ', @curvar=@cur output

FETCH NEXT FROM @cur
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @cur
END




Резюме(более IMHO, чем обязательные требования)
Динамический запрос очень полезная и иногда просто незаменимая вещь, НО способы его реализации и конкретно через вызов в отдельном пакете с правами пользователя, вызвавшего процедуру, принижают его практическое МАССОВОЕ применение.
Glory

Комментарии


  • http://tebe-nado.ru - дешевый интернет шопинг

  • http://tebe-nado.ru - интернет магазин шопинг стайл

  • http://tebe-nado.ru - интернет шопинг для женщин

  • христианские футболки заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/product/cushion_antistress/726970_cushion_antistress?color=white>Популярные майки / Каталог / Bring Me The Horizon</a> и многое другое!
    *&$*

  • http://tebe-nado.ru - интернет шопинг америке

  • футболки поло нанесение заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/product/hoodie/114491_hoodie?color=white&group=>Популярные майки / Каталог / Очки в стразах</a> и многое другое!
    *&$*

  • спортивный костюм http://tebe-nado.ru

  • футболки с флагами заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/product/man_tank_full/1056841_man_tank_full?color=white>Популярные майки / Каталог / Love</a> и многое другое!
    *&$*

  • футболки http://tebe-nado.ru

  • верхняя одежда http://tebe-nado.ru

  • шорты и брюк http://tebe-nado.ru

  • посуда http://tebe-nado.ru

  • мужская розовая футболка заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/catalog/view/interior/mayki_kino/metalocalypse>Популярные майки / Каталог / Кино, мультфильмы и ТВ / Metalocalypse</a> и многое другое!
    *&$*

  • сайт маек заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/catalog/type/smock/mayki_sport?page=2>Популярные майки / Каталог / Спорт</a> и многое другое!
    *&$*

  • однотонные футболки заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/catalog/view/accessory/names/here_was>Популярные майки / Каталог / Имена / Имена любимых парней</a> и многое другое!
    *&$*

  • майки bikkembergs заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,<a href=http://mayki-ok.ru/catalog/type/tablecloth_square/Rock/ramones>Популярные майки / Каталог / Рок / Ramones</a> и многое другое!
    *&$*

  • http://bit.ly/2gQNdP3 - купить зимнюю куртку мужскую
    заказать!
    Наши товары: одежда, футболки, майки, толстовки, свитшоты, верхняя одежда, шорты, спортивные брюки, одежда для беременных,сувениры
    и многое другое!

  • http://bit.ly/2ybLFKY - Модная одежда-Скидки до 90%

  • в том смысле, что
    временную продцедуру (имя с #)

  • еще можно динамически создать продцедуру,
    или "оборачивать" наш запрос в продцедуру

    declare @sql_text nvarchar(max)

    set @sql_text=N'create proc #temp2 @id int
    as
    begin
    select * from My_Table where Id=@id
    end'

    exec (@sql_text)

    exec #temp2 1

    drop procedure #temp2

  • огромное спасибо, как раз все, что нужно здесь нашлось!!!

  • спасибки! помогло разобраться)

  • Another good reference in English
    http://www.sommarskog.se/dynamic_sql.html

  • 09 декабря 2009, 18:08 Дмитрий Исаев

    тверской,
    "включите в неё (если возможно) механизм сохранения результата работы exec в таблицу" - а это зачем?? Просто:
    set @sql="INSERT INTO ... дальше ваши инструкции"
    exec (@sql)

  • 12 августа 2009, 17:01 Ольга Корн

    Спасибо. Очень нужная информация.

  • Молодцы!

  • 03 августа 2009, 11:25 Филипп Вульфович

    Большое спасибо! Статья очень помогла! Как раз то, что я искал!

  • спасибо за статью.

  • Если у пользователей нет прав на доступ к таблицам, о динамических запросах, выбирающих данные из постоянных таблиц, можно забыть:( Вот это я считаю тупостью. Переборщили с безопасностью.

  • то что нужно

  • 18 марта 2009, 18:03 тверской

    Glory, отличная, полезная статья
    включите в неё (если возможно) механизм сохранения результата работы exec в таблицу

  • Хорошая статья, спасибо.

  • большое, человеческое спасибо! это действительно очень полезная информация ))

  • пасибки

  • ё-моё, как же все сложно и через задницу... за ответ, конечно, огромное спасибо, сам бы еще долго искал, но как же это все ужасно по сравнению с постгресовскими функциями...

  • Спасибо автору за указанные грабли

  • 14 мая 2007, 13:21 Vladimir410

    это полезная статья, спасибо!

  • Очень помогло, огромное спасибо.

  • 03 мая 2007, 18:17 сибирская кошка

    статья помогла, thanks

    вот тут у вас опечатка в имени переменной: @table_name и @TableName

    DECLARE @SQL varchar(8000), @table_name varchar(10)
    SET @SQL = 'SELECT * FROM ' + @TableName

  • 17 апреля 2007, 14:53 Dneprovsky Artem

    Я тоже новичёк. :D
    Очень помогла статья! Почёт и уважение автору!

  • Краткость-сестра таланта

  • Ни грамма лишнего и все по существу. Одобрям.)

  • То что надо. Кратко и доступно

  • Спасибо

  • Danke

  • Молодец, автор! Написано просто и доступным языком. Прочитал, взял и сделал - вот бы все инструкции были так написаны.

  • огромный респект:)

  • Зачот!

  • огромное спасибо!

  • Коротко и ясно...

  • Оппа, похоже я поторопился...

  • А если запрос надо использовать в функции, то как тогда?

  • Спасибо, очень содержательное и толковое описание. А главное, полезное!!!

  • Очень полезная информация, спасибо за примеры :))

  • Огромное спасибо, очень ценная статья, особенно последнее примечание

  • Просто очень полезная хитрость

  • Пребольшое вам спасибо, оч помогло.

  • Пример с получением выходных значений мне помог.

  • Спасибо, очень помогло...

  • Спасибо! Третья часть - супер! Замучил всех коллег :-)

  • Просто класс, особенно - Часть 3

  • !!!

  • познавательно!

  • спасибо

  • Спасибо помогло найти новые решения наболевших проблем

  • Очень помогло, спасибо!

  • спасибо, то что надо!

  • Весьма полезно.

  • Спасибо! очень полезно

  • Постоянно пользуюсь

  • Постоянно пользуюсь

  • я сегодня это долго искал нужный пример

  • спасибо огромное. избавили форум от новой старой темы:)

  • 04 марта 2005, 15:01 Евгений Веприков

    Нормально)

  • То что надо!!!

  • С курсором, это то что я искал

  • Полезно. Очень!

  • Пригодилось)

  • Последнее примечание очень полезно.

  • Последнее примечание очень полезно.

  • Круто

  • Действительно полезное разъяснение для новичков, коим я и являюсь :)



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Динамический запрос или "переменная @Tablename"