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

Комментарии




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

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