Ещё одна приятная фишка, которая появится в новой версии SQL Server ( aka Denali ) - это параметр WITH RESULT SETS инструкции EXECUTE. Этот параметр позволяет не только делать выборку из процедуры, но и изменять результирующий рекордсет.
На форуме SQL.RU достаточно часто возникает вопрос, как сделать "Select из результата выполнения хранимой процедуры". В местном FAQ есть даже отделная ссылка с вариантами решения этой задачи - http://www.sql.ru/faq/faq_topic.aspx?fid=416.
Прежде, чем показать, как на лету менять рекордсет из процедуры, а именно типы полей и их имена в SQL Server 2011, вспомним, как решить эту задачу в текущих версиях SQL Server.
Для начала создадим тестовую таблицу из которой и будем делать выборку в теле процедуры:
if object_id ( 'dbo.Table01', 'U' ) is not null
drop table dbo.Table01
go
create table dbo.Table01 ( id int identity primary key
, val varchar(50)
, dt datetime
)
go
insert into dbo.Table01
select 'Val01', '20110801'
union all
select 'Val02', '20110802'
union all
select 'Val03', '20110803'
union all
select 'Val04', '20110804'
union all
select 'Val05', '20110805'
go
select * from dbo.Table01
А теперь создадим процедуру, которая делает выборку из этой таблицы:
if object_id ( 'dbo.Proc01', 'P' ) is not null
drop procedure dbo.Proc01
go
--Процедура, которая возвращает рекордсет из нашей таблицы
create procedure dbo.Proc01
as
set nocount on
select * from dbo.Table01
go
--Вызов процедуры
exec dbo.Proc01
А теперь предположим, что нам нужно использовать результат этой процедуры, при этом нам нужно изменить название поля
val на
MyVal. Т.к. в текущих версиях мы это сделать напрямую не можем, то есть несколько обходных решений:
1) Одно из решений - это обернуть наш запрос в виде табличной функции
if object_id ( 'dbo.func01', 'TF' ) is not null
drop function dbo.func01
go
--Табличная функция
create function dbo.func01()
returns @tbl table ( id int
, MyVal varchar(50)
, dt datetime
)
as
begin
insert into @tbl
select * from dbo.Table01
return
end
go
--Выборка из нашей функции
select * from dbo.func01()
2) Ещё один вариант - это вывод результат процедуры во временную таблицу и уже после делать выборку уже из этой временной таблицы:
if object_id ( 'tempdb..#tmp_table', 'U' ) is not null
drop table #tmp_table
go
--Временная таблица, для загрузки рекордсета из нашей процедуры
create table #tmp_table ( id int
, MyVal varchar(50)
, dt datetime
)
go
--Вставляем записи
insert into #tmp_table
exec dbo.Proc01
go
--Последующая выборка из временной таблицы
select * from #tmp_table
3) Третий способ - это использование функций
OPENROWSET либо
OPENQUERY
--Создадим линкед сервер, который ссылается на этот же сервер БД
exec master..sp_addlinkedserver N'(local)'
, N'SQL Server'
go
--Включаем настройку 'Ad Hoc Distributed Queries', т.к. иначе получим ошибку:
---------------------------------------
--Msg 15281, Level 16, State 1, Line 2
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
--because this component is turned off as part of the security configuration for this server.
--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
--For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
---------------------------------------
exec sp_configure 'show advanced options', 1
reconfigure
go
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
--Выборка из процедуры, через OPENROWSET
select id, val as MyVal, dt from openrowset ( 'SQLNCLI'
, 'Server=(local);Trusted_Connection=yes;'
, 'exec test.dbo.Proc01'
)
go
--Выборка из процедуры, через OPENQUERY
select id, val as MyVal, dt from openquery ( [(local)], 'exec test.dbo.Proc01' )
go
Ещё несколько вариантов можно подглядеть у Erland Sommarskog, SQL Server MVP -
http://www.sommarskog.se/share_data.html
А теперь пример с использованием "фишек" SQL Denali:
exec dbo.Proc01
with result sets
(
( id int
, MyVal varchar(50)
, dt datetime
)
);
Но, как я уже писал выше, мы можем не только на лету менять имена полей, но и их тип:
exec dbo.Proc01
with result sets
(
( id int
, MyVal varchar(50)
, dt date
)
);
Ссылки по теме:
Подписаться