В SQL Server есть ограничение на инструкцию INSERT EXEC - она не может быть вложенной. Т.е. если в теле процедуры мы уже используем код INSERT EXEC, то рекордсет из этой процедуры мы не сможем вставить в таблицу. На Microsoft Connect есть фитбек с этой проблемой (Cannot have nested INSERT ... EXEC) и совсем недавно эту проблему закрыли с пометкой as Won't Fix.
Но, что делать, если нам все-таки необходимо вывести результат работы процедуры в таблицу? Именно тому, как обойти одно из ограничений сиквела и посвящён этот пост.
Для иллюстрации создадим тестовую БД TestDB и две процедуры, одна будет возвращать небольшой рекордсет, а во второй мы будем вызывать эту процедуру с использованием инструкции INSERT EXEC.
create database TestDB
go
use TestDB
go
if object_id ( 'dbo.TestProc01', 'P' ) is not null
drop procedure dbo.TestProc01
go
create procedure dbo.TestProc01
as
set nocount on
declare @t table ( i int )
insert into @t
values (1),(2),(3)
select * from @t
go
if object_id ( 'dbo.TestProc02', 'P' ) is not null
drop procedure dbo.TestProc02
go
create procedure dbo.TestProc02
as
set nocount on
declare @t table ( i int )
insert into @t
exec dbo.TestProc01
select * from @t
go
Дальше небольшой скрипт, который и эмулирует, озвученную выше, ошибку:
declare @t table ( i int )
insert into @t
exec dbo.TestProc02
А теперь несколько способов обойти эту ошибку:
1) Первый и самый правильный
По возможности не использовать вложенных инструкций INSERT EXEC, либо вместо вызова процедуры, в которой уже используется такая конструкция, взять часть скрипта из тела этой самой процедуры. Как правило, это достаточно легко можно реализовать. В моём примере достаточно заменить вызов процедуры dbo.TestProc02 на dbo.TestProc01.
2) Используем OPENQUERY или OPENROWSET
Для этого нам потребуется создать
Linked Server с ссылкой нашего сервера БД на самого себя ( в моём случае это IP
127.0.0.1).
use master
go
exec sp_addlinkedserver N'127.0.0.1'
, N'SQL Server';
go
use TestDB
go
declare @t table ( i int )
insert into @t
select * from OpenQuery ( [127.0.0.1], 'TestDB.dbo.TestProc02' )
select * from @t
3) Используем распределённый запрос
declare @t table ( i int )
insert into @t
exec [127.0.0.1].TestDB.dbo.TestProc02
select * from @t
go
--либо:
declare @t table ( i int )
insert into @t
exec ( 'TestDB.dbo.TestProc02' ) at [127.0.0.1]
select * from @t
go
Не забываем включить службу
Координатор распределенных транзакций Иначе получим ошибку:
4) Используем процедуру xp_cmdshell и утилиту SQLCMD
Но для начала включим использование процедуры xp_cmdshell
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
go
А теперь выгружаем результат работы процедуры dbo.TestProc02 в таблицу:
declare @t table ( val varchar(100) )
insert into @t
exec master..xp_cmdshell 'sqlcmd -E -q "exec TestDB.dbo.TestProc02" -h -1 -W'
select val from @t
where val is not null
5) Используем процедуру xp_cmdshell и утилиту BCP
--Выгружаем результат процедуры dbo.TestProc02 на диск
exec xp_cmdshell 'bcp "exec TestDB.dbo.TestProc02" queryout "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'
--Создадим таблицу для получения результата
if object_id ( 'dbo.tmpMyResult', 'U' ) is not null
drop table tmpMyResult
go
create table tmpMyResult ( val int )
--Загружаем результат с диска в таблицу
exec xp_cmdshell 'bcp TestDB.dbo.tmpMyResult in "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'
--Смотрим
select * from tmpMyResult
6) Используем CLR
Но этот вариант я не буду рассматривать в рамках этого поста.
Подписаться