Всем известно ограничение Transact-SQL, состоящее в следующем. Есть хранимая процедура, которая возвращает таблицу. Если требуется, чтобы эта таблица участвовала в каком-либо запросе, то приходится сохранять результаты работы процедуры во временной таблице с помощью инструкции "insert into <Table Name> exec <ProcName>" Это неудобно, требуется больше кода.
К тому же инструкция "insert into <Table Name> exec <ProcName>" имеет ограничение. Многие разработчики сталкивались с такой проблемой: в коде процедуры <ProcName> уже имеется инструкция "insert into ... exec ...". Тогда попытка сохранить результаты <ProcName> приводит к ошибке. В сообщении говорится, что конструкция "insert into ... exec ..." не может быть вложенной. Можно пытаться обойти эту проблему, используя функцию openquery, которая использует в качестве связанного сервера линк, который смотрит на исходный сервер. Однако такой метод не всегда сработает: функция может завершиться с ошибкой, если в процедуре используются временные таблицы, к тому же openquery не поддерживает параметры.
Один из подходов к решению проблемы состоит в следующем. Напишем табличную функцию, которая получает в качестве параметра текстовое sql-выражение и возвращает набор записей, который возщвращает sql-код. Как известно функции имеют массу ограничений по сравнению с хранимыми процедурами. Однако в них можно применять расширенные хранимые процедуры sp_OA...
План функции таков. Через sp_OACreate мы создадим ADO-соединение и ADO-команду. Для команды инициализируем свойство CommandText нашим sql-кодом. Далее применим метод Execute, инициализировав ADO-набор записей. После этого пройдемся в цикле по набору записей, наполнив выходную таблицу.
Предварительно создадим табличную функцию, которая будет логировать возможные ошибки OLE:
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.ProcessOleError', N'TF' ) is null
exec ( N'create function dbo.ProcessOleError () returns @tab table ( i int ) as begin return end' )
go
alter function dbo.ProcessOleError ( @step varchar ( 100 ), @obj int )
returns @tab table ( src varchar ( 300 ), descr varchar ( 300 ) )
as
begin
declare @src varchar ( 300 ), @desc varchar ( 300 )
exec sp_OAGetErrorInfo @obj, @src out, @desc out
insert into @tab ( src, descr )
values ( N'статус: ' + @step + N'; источник: ' + isnull ( @src, 'неизвестно' ), N'описание: ' + isnull ( @desc, 'неизвестно' ) )
return
end
go
Ниже код табличной функции dbo.GetQueryResult:
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.GetQueryResult', N'TF' ) is null
exec ( N'create function dbo.GetQueryResult () returns @tab table ( i int ) as begin return end' )
go
alter function dbo.GetQueryResult ( @txt nvarchar ( max ) )
returns @tab table
(
col0 varchar ( 300 ),
col1 varchar ( 300 )
)
as
begin declare @res int, @conn int, @cmd int, @prop int, @propInst int, @propSearch int, @propAsync int, @ADOrs int
exec @res = sp_OACreate 'ADODB.Connection', @conn out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'создание соединения', @conn )
goto GarbageCollector
end
exec @res = sp_OASETProperty @conn, 'ConnectionString', 'Provider=SQLNCLI11; Server=EDYNAK\SHADOW; Database=master; Trusted_Connection=yes;'
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'строка подключения', @conn )
goto GarbageCollector
end
exec @res = sp_OAMethod @conn, 'Open'
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'открытие соединения', @conn )
goto GarbageCollector
end
exec @res = sp_OACreate 'ADODB.Command', @cmd out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'создание команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OASETProperty @cmd, 'ActiveConnection', @conn
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'соединение для команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OASETProperty @cmd, 'CommandText', @txt
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'текст команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OACreate 'ADODB.Recordset', @ADOrs out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'набор записей', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAMethod @cmd, 'Execute', @ADOrs out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'выполнение запроса', @cmd )
goto GarbageCollector
end
declare @h int = 1, @data varchar ( 300 ), @obj varchar ( 300 )
declare @src varchar ( 300 ), @desc varchar ( 300 )
declare @eof bit = 0
while @eof <> 1
begin
exec @res = sp_OAGetProperty @ADOrs, 'Fields(0).Value', @data out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'первое поле', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAGetProperty @ADOrs, 'Fields(1).Value', @obj out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'второе поле', @ADOrs )
goto GarbageCollector
end
insert into @tab ( col0, col1 )
values ( @data, @obj )
exec @res = sp_OAMethod @ADOrs, 'MoveNext', null
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'переход на строку', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAGetProperty @ADOrs, 'EOF', @eof out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'признак окончания набора', @ADOrs )
goto GarbageCollector
end
end
GarbageCollector:
exec sp_OADestroy @conn
exec sp_OADestroy @cmd
exec sp_OADestroy @prop
exec sp_OADestroy @propInst
exec sp_OADestroy @propSearch
exec sp_OADestroy @propAsync
exec sp_OADestroy @ADOrs
return
end
go
Испытаем функцию. Например, для того чтобы получить список всех объектов всех баз данных одним запросом, можно использовать такой код:
select *
from dbo.GetQueryResult
( '
declare @sql nvarchar ( max ) = N'''', @name nvarchar ( max )
declare cur cursor local static forward_only for
select name
from sys.databases
where database_id > 4
open cur
fetch next from cur into @name
while @@fetch_status = 0
begin
set @sql = @sql + case when @sql = N'''' then N'''' else N'' union all '' end + N''
select '''''' + @name + '''''', name collate Cyrillic_General_CI_AI
from '' + quotename ( @name ) + ''.sys.tables
''
fetch next from cur into @name
end
close cur
deallocate cur
exec sp_executesql @sql
')
Теперь можно использовать этот запрос для соединения с другими таблицами, вставками в таблицы. Можно передать в паметр функции и sql-выражение, содержащее вызов любой процедуры с параметрами, возвращающей набор записей. В коде функции я для примера сделал возврат только двух столбцов. При необходимости можно добавить большее число столбцов. Для работы функции на серверном компьютере должна быть установлена библиотека Microsoft ActiveX Data Objects.
К тому же инструкция "insert into <Table Name> exec <ProcName>" имеет ограничение. Многие разработчики сталкивались с такой проблемой: в коде процедуры <ProcName> уже имеется инструкция "insert into ... exec ...". Тогда попытка сохранить результаты <ProcName> приводит к ошибке. В сообщении говорится, что конструкция "insert into ... exec ..." не может быть вложенной. Можно пытаться обойти эту проблему, используя функцию openquery, которая использует в качестве связанного сервера линк, который смотрит на исходный сервер. Однако такой метод не всегда сработает: функция может завершиться с ошибкой, если в процедуре используются временные таблицы, к тому же openquery не поддерживает параметры.
Один из подходов к решению проблемы состоит в следующем. Напишем табличную функцию, которая получает в качестве параметра текстовое sql-выражение и возвращает набор записей, который возщвращает sql-код. Как известно функции имеют массу ограничений по сравнению с хранимыми процедурами. Однако в них можно применять расширенные хранимые процедуры sp_OA...
План функции таков. Через sp_OACreate мы создадим ADO-соединение и ADO-команду. Для команды инициализируем свойство CommandText нашим sql-кодом. Далее применим метод Execute, инициализировав ADO-набор записей. После этого пройдемся в цикле по набору записей, наполнив выходную таблицу.
Предварительно создадим табличную функцию, которая будет логировать возможные ошибки OLE:
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.ProcessOleError', N'TF' ) is null
exec ( N'create function dbo.ProcessOleError () returns @tab table ( i int ) as begin return end' )
go
alter function dbo.ProcessOleError ( @step varchar ( 100 ), @obj int )
returns @tab table ( src varchar ( 300 ), descr varchar ( 300 ) )
as
begin
declare @src varchar ( 300 ), @desc varchar ( 300 )
exec sp_OAGetErrorInfo @obj, @src out, @desc out
insert into @tab ( src, descr )
values ( N'статус: ' + @step + N'; источник: ' + isnull ( @src, 'неизвестно' ), N'описание: ' + isnull ( @desc, 'неизвестно' ) )
return
end
go
Ниже код табличной функции dbo.GetQueryResult:
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.GetQueryResult', N'TF' ) is null
exec ( N'create function dbo.GetQueryResult () returns @tab table ( i int ) as begin return end' )
go
alter function dbo.GetQueryResult ( @txt nvarchar ( max ) )
returns @tab table
(
col0 varchar ( 300 ),
col1 varchar ( 300 )
)
as
begin declare @res int, @conn int, @cmd int, @prop int, @propInst int, @propSearch int, @propAsync int, @ADOrs int
exec @res = sp_OACreate 'ADODB.Connection', @conn out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'создание соединения', @conn )
goto GarbageCollector
end
exec @res = sp_OASETProperty @conn, 'ConnectionString', 'Provider=SQLNCLI11; Server=EDYNAK\SHADOW; Database=master; Trusted_Connection=yes;'
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'строка подключения', @conn )
goto GarbageCollector
end
exec @res = sp_OAMethod @conn, 'Open'
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'открытие соединения', @conn )
goto GarbageCollector
end
exec @res = sp_OACreate 'ADODB.Command', @cmd out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'создание команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OASETProperty @cmd, 'ActiveConnection', @conn
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'соединение для команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OASETProperty @cmd, 'CommandText', @txt
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'текст команды', @cmd )
goto GarbageCollector
end
exec @res = sp_OACreate 'ADODB.Recordset', @ADOrs out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'набор записей', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAMethod @cmd, 'Execute', @ADOrs out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'выполнение запроса', @cmd )
goto GarbageCollector
end
declare @h int = 1, @data varchar ( 300 ), @obj varchar ( 300 )
declare @src varchar ( 300 ), @desc varchar ( 300 )
declare @eof bit = 0
while @eof <> 1
begin
exec @res = sp_OAGetProperty @ADOrs, 'Fields(0).Value', @data out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'первое поле', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAGetProperty @ADOrs, 'Fields(1).Value', @obj out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'второе поле', @ADOrs )
goto GarbageCollector
end
insert into @tab ( col0, col1 )
values ( @data, @obj )
exec @res = sp_OAMethod @ADOrs, 'MoveNext', null
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'переход на строку', @ADOrs )
goto GarbageCollector
end
exec @res = sp_OAGetProperty @ADOrs, 'EOF', @eof out
if @res <> 0
begin
insert into @tab ( col0, col1 )
select src, descr
from dbo.ProcessOleError ( N'признак окончания набора', @ADOrs )
goto GarbageCollector
end
end
GarbageCollector:
exec sp_OADestroy @conn
exec sp_OADestroy @cmd
exec sp_OADestroy @prop
exec sp_OADestroy @propInst
exec sp_OADestroy @propSearch
exec sp_OADestroy @propAsync
exec sp_OADestroy @ADOrs
return
end
go
Испытаем функцию. Например, для того чтобы получить список всех объектов всех баз данных одним запросом, можно использовать такой код:
select *
from dbo.GetQueryResult
( '
declare @sql nvarchar ( max ) = N'''', @name nvarchar ( max )
declare cur cursor local static forward_only for
select name
from sys.databases
where database_id > 4
open cur
fetch next from cur into @name
while @@fetch_status = 0
begin
set @sql = @sql + case when @sql = N'''' then N'''' else N'' union all '' end + N''
select '''''' + @name + '''''', name collate Cyrillic_General_CI_AI
from '' + quotename ( @name ) + ''.sys.tables
''
fetch next from cur into @name
end
close cur
deallocate cur
exec sp_executesql @sql
')
Теперь можно использовать этот запрос для соединения с другими таблицами, вставками в таблицы. Можно передать в паметр функции и sql-выражение, содержащее вызов любой процедуры с параметрами, возвращающей набор записей. В коде функции я для примера сделал возврат только двух столбцов. При необходимости можно добавить большее число столбцов. Для работы функции на серверном компьютере должна быть установлена библиотека Microsoft ActiveX Data Objects.
