Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL. Думаю, большинство здесь изложенного может пригодиться только в приложениях со сложной SQL логикой и объемными процедурами. Не берусь утверждать, что данные методы самые эффективные. Это всего лишь то, что я использую в своей работе. Всё это работает под Microsoft SQL Server 2008.
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.
Пусть процедура, из которой нам нужно получить данные будет такой:
Один из самых простых методов. Используем конструкцию
С помощью записи в ранее созданную таблицу. Здесь придется добавлять insert в процедуру:
По сути мы перенесли строку insert внутрь процедуры.
Я не рассматриваю возможность передачи через постоянные таблицы, т.к. если это требуется, то задача не относиться к данной теме. Если же нет, то мы получаем лишние проблемы с блокировкой и идентификацией между сессиями.
По сути, является доработкой второго метода. Чтобы упростить поддержку создаем пользовательский тип таблицы. Выглядит это примерно так:
Усложнение третьего метода, позволяющее создавать таблицу с ограничениями и индексами. В отличии от предыдущего работает под Microsoft SQL Server 2005.
Однако обычно временная колонка delmy не используется, вместо неё таблица создается просто с одним первым столбцом (здесь с p1).
Этот метод использует предварительно созданные процедуры. Он основан на включении динамического SQL-запроса в запускаемую процедуру. Однако является достаточно простым в использовании.
Для его использования процедуры необходимо обработать следующим образом:
1. В начало процедуры включить строки:
2. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с
3. В конец процедуры включить строку:
Для нашего примера мы получаем:
Запуск осуществляется так:
Поскольку генерируемый SQL это не всегда хорошо, то приведенный пример лучше подходит для небольших инструкций. Если кода достаточно много, то можно либо вынести его в отдельную процедуру и из динамической части осуществлять только exec вызов, либо перезалить данные в новые временные таблицы. В последнем случае, конечно, происходит ещё одно «лишнее» копирование, но часто бывает так, что на этом этапе мы можем предварительно сгруппировать результат и выбрать только нужные поля для дальнейшей обработки (например, если в каком-либо случае не требуется все возвращаемые данные).
Функции
Работа построена таким образом, что запуск
Можно воспользоваться передачей параметра из функции (
На эту тему существует статья.
Использовать курсор для этой задачи я не вижу смысла, только если изначально требуется именно курсор. А вот XML выглядит перспективным. Здесь очень интересные результаты тестов на производительность.
Интересно услышать какие вы используете способы упрощения этой задачи :)
UPD 31.03.2014: Скорректировал пост по идеям из комментариев
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.
Пусть процедура, из которой нам нужно получить данные будет такой:
create procedure Proc1
as
begin
select 1 p1, 'b' p2
end
1 Метод
Один из самых простых методов. Используем конструкцию
insert ... exec ...
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
insert #t1
exec Proc1
select * from #t1
Плюсы и минусы:
- Передаваемые поля перечисляются 2 раза (это внутренний
select
, внешнее создание таблицы иinsert
). Плюс перечисление полей происходит при каждом новом аналогичном вызове. (Я добавляю данный критерий, т.к. при большом количестве правок и множестве мест вызова процедуры, процесс изменения выводимых данных становится очень трудоемким) - Имеет серьезное ограничение – мы можем получить только одну таблицу
- Для работы процедуры в режиме простого вывода не требуются дополнительные действия, достаточно запустить
exec Proc1
безinsert
2 Метод
С помощью записи в ранее созданную таблицу. Здесь придется добавлять insert в процедуру:
create procedure Proc1
as
begin
insert #t1(p1, p2)
select 1 p1, 'b' p2
end
По сути мы перенесли строку insert внутрь процедуры.
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
exec Proc1
select * from #t1
Плюсы и минусы:
- Передаваемые поля перечисляются 2 раза, и еще по одному перечислению на каждое новое использование
- Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от
Proc1
таблицы, либо определять, когда их выводить внутриProc1
. Например, по признаку не существования таблицы для вставки:
alter procedure Proc1
as
begin
declare @show bit
if object_id(N'tempdb..#t1',N'U') is null
begin
set @show = 1
create table #t1(p1 int, p2 varchar(max))
end
insert #t1(p1, p2)
select 1 p1, 'b' p2
if (@show = 1)
begin
select * from #t1
end
end
Я не рассматриваю возможность передачи через постоянные таблицы, т.к. если это требуется, то задача не относиться к данной теме. Если же нет, то мы получаем лишние проблемы с блокировкой и идентификацией между сессиями.
3 Метод
По сути, является доработкой второго метода. Чтобы упростить поддержку создаем пользовательский тип таблицы. Выглядит это примерно так:
create type tt1 as table(p1 int, p2 varchar(max))
go
create procedure Proc1
as
begin
insert #t1(p1, p2)
select 1 p1, 'b' p2
end
go
-- используем:
declare @t1 tt1
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
select *
into #t1
from @t1
exec Proc1
select * from #t1
Плюсы и минусы:
- Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
- Для организации непосредственного вывода результата также требуются дополнительные действия
- Есть небольшие сложности с созданием индексов и ограничений, т.к. их мы не можем создать с помощю конструкции
select ... into
4 Метод
Усложнение третьего метода, позволяющее создавать таблицу с ограничениями и индексами. В отличии от предыдущего работает под Microsoft SQL Server 2005.
create procedure Proc1
as
begin
insert #t1(p1, p2)
select 1 p1, 'b' p2
end
go
create procedure Proc1_AlterTable
as
begin
alter table #t1 add p1 int, p2 varchar(max)
alter table #t1 drop column delmy
end
go
-- используем:
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(delmy int)
exec Proc1_AlterTable
exec Proc1
select * from #t1
Однако обычно временная колонка delmy не используется, вместо неё таблица создается просто с одним первым столбцом (здесь с p1).
Плюсы и минусы:
- Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
- Для непосредственного вывода результата также требуются дополнительные действия
- Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции
alter table #t1
, и процесс ожидает полного завершенияProc1
(неProc1_AlterTable
!) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)
5 Метод
Этот метод использует предварительно созданные процедуры. Он основан на включении динамического SQL-запроса в запускаемую процедуру. Однако является достаточно простым в использовании.
Для его использования процедуры необходимо обработать следующим образом:
1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin
2. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с
#Output
(Например into #Output
, into #Output5
, into #OutputMySelect
). Если процедура не создает результирующего набора, то действие не требуется3. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin
Для нашего примера мы получаем:
create procedure Proc1
as
begin
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin
select 1 p1, 'b' p2
into #Output1
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin
end
Запуск осуществляется так:
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun'
select * from #InclusionOutput1
', 1, '#InclusionOutput'
exec Proc1
Поскольку генерируемый SQL это не всегда хорошо, то приведенный пример лучше подходит для небольших инструкций. Если кода достаточно много, то можно либо вынести его в отдельную процедуру и из динамической части осуществлять только exec вызов, либо перезалить данные в новые временные таблицы. В последнем случае, конечно, происходит ещё одно «лишнее» копирование, но часто бывает так, что на этом этапе мы можем предварительно сгруппировать результат и выбрать только нужные поля для дальнейшей обработки (например, если в каком-либо случае не требуется все возвращаемые данные).
Функции
util.InclusionRun
передаются 3 параметра:@sql
– SQL-скрипт, который выполниться внутри вызываемой процедуры@notShowOutput
– если = 1, то блокировать вывод таблиц, начинающихся с#Output
@replaceableTableName
– (по умолчанию ='#Output'
) задать префикс в имени таблиц используемых в@sql
, для замены его на соответствующую#Output*
таблицу в скрипте. Например, если задать#InclusionOutput
, и в процедуре созданы две таблицы#Output55
и#Output0A
, то в@sql
можно обратиться к#Output55
как к#InclusionOutput1
, а к#Output0A
как к#InclusionOutput2
Работа построена таким образом, что запуск
Proc1
, без предварительного запуска util.InclusionRun
приводит к естественной работе процедуры с выводом всех данных, которые она выводила до обработки.Нюансы использования:
- Накладывает ограничения на использование инструкции
return
в процедуре, т.к. перед ней необходим запускutil.InclusionEnd
- Выводящие результат select'ы из запускаемых процедур выводят результат раньше, чем даже те #Output-таблицы, которые были созданы до их вызова (это логично, т.к. вывод происходит только в
util.InclusionEnd
)
Плюсы и минусы:
- Передаваемые поля перечисляются один раз, при этом каждое новое использование не добавляет сложности
- Для непосредственного вывода результата не требуется никаких действий
- Необходимо помнить и учитывать нюансы использования
- Из-за дополнительных процедур выполняется больше инструкций, что может снизить быстродействие при частых вызовах (я думаю, что при запуске реже одного раза в секунду этим можно пренебречь)
- Возможно, может усложнить понимание кода для сотрудников не знакомых с данным методом: процедура приобретает два exec-вызова и неочевидность того, что все
#Output
-таблицы будут выведены - Позволяет легко организовать модульное тестирование без внешних инструментов
Демонстрация использования:
Скрытый текст
Код:
Результат:
if object_id('dbo.TestInclusion') is not null drop procedure dbo.TestInclusion
go
create procedure dbo.TestInclusion
@i int
as
begin
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin
if object_id('tempdb..#tmp2', 'U') is not null drop table #tmp2
select @i myI
into #tmp2
if object_id('tempdb..#tmp3', 'U') is not null drop table #tmp3
select @i + 1 myI
into #tmp3
select *
into #Output0 --На вывод (выводится в util.InclusionEnd)
from #tmp2
union all
select *
from #tmp3
select 'процедура TestInclusion' alt
into #OutputQwerty --На вывод (выводится в util.InclusionEnd)
exec util.InclusionEnd --выводит все таблицы начинающиеся с #Output в порядке из создания после запуска util.InclusionBegin
end
go
set nocount on
set ansi_warnings off
if object_id('tempdb..#ttInclusionParameters', 'U') is not null drop table #ttInclusionParameters
go
select 'Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"'
exec dbo.TestInclusion 2
go
select 'Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5'
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun '
select sum(myI) testSum
from #InclusionOutput1
', 1, '#InclusionOutput'
exec dbo.TestInclusion 2
Результат:
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"
myI
-----------
2
3
alt
-----------------------
процедура TestInclusion
------------------------------------------------------------------------------------------------------
Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5
testSum
-----------
5
Сами функции:
Скрытый текст
if not exists(select top 1 null from sys.schemas where name = 'util')
begin
exec ('create schema util')
end
go
alter procedure util.InclusionBegin
as
begin
/*
Инструкция для использования:
1. Обработка процедуры данные которой необходимо использовать
1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin
1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
Дополнительно см. коментарии внутри util.InclusionRun
*/
set nocount on
set ansi_warnings off
declare @lvl int
if object_id('tempdb..#ttInclusionParameters', 'U') is not null
begin
select @lvl = max(lvl)
from #ttInclusionParameters
--Добавляем null задание, для предотвращения запуска скрипта во вложенных процедурах с данным механизмом
if (@lvl is not null)
begin
insert #ttInclusionParameters(lvl, pr)
select @lvl+1 lvl, null pr
end
end
if object_id('tempdb..#ttInclusion', 'U') is not null
begin
--запоминаем все уже существующие таблицы #Output, чтобы в util.InclusionEnd не выводить их
insert #ttInclusion(lvl, i)
select isnull(@lvl, 0), so.object_id i
from tempdb.sys.objects so
where so.type = 'U'
and so.name like '#[^#]%'
and object_id('tempdb..' + so.name, 'U') is not null
and not exists (select top 1 null from #ttInclusion where i = so.object_id)
end
end
GO
go
alter procedure util.InclusionEnd
as
begin
/*
Инструкция для использования:
1. Обработка процедуры данные которой необходимо использовать
1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin
1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
Дополнительно см. коментарии внутри util.InclusionRun
*/
set nocount on
set ansi_warnings off
----------------------------------------------------------------------------------------------------
--считываем параметры
declare @lvl int
, @p0 varchar(max) --(@sql) sql скрипт который необходимо выполнить
, @p1 varchar(max) --(@notShowOutput) если равно '1' хотя бы у одного из существующих вложенности заданий, то НЕ выводим #Output, иначе селектим их
, @p2 varchar(max) --(@replaceableTableName) заменяемый префекс таблицы
if object_id('tempdb..#ttInclusionParameters', 'U') is not null
begin
--считываем глобальные параметры
select @p1 = max(val)
from #ttInclusionParameters
where pr = 1
--находим уровень на котором наше задание (max(lvl) - это уровень с null который мы добавили в util.InclusionBegin)
select @lvl = max(lvl) - 1
from #ttInclusionParameters
if @lvl is not null
begin
--считываем
select @p0 = max(case when pr = 0 then val end)
, @p2 = max(case when pr = 2 then val end)
from #ttInclusionParameters
where lvl = @lvl
having max(pr) is not null
--удаляем задание на скрипт, а если его нет, то только null-задание
delete #ttInclusionParameters
where lvl >= @lvl and (lvl > @lvl or @p0 is not null)
end
end
----------------------------------------------------------------------------------------------------
--выбираем все созданные таблицы #Output
if object_id('tempdb..#InclusionOutputs', 'U') is not null drop table #InclusionOutputs
create table #InclusionOutputs(i int, tableName varchar(max), num int)
if object_id('tempdb..#ttInclusion', 'U') is not null
begin
insert #InclusionOutputs(i, tableName, num)
select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
from tempdb.sys.objects so
where so.type = 'U'
and so.name like '#[^#]%'
and object_id('tempdb..' + so.name, 'U') is not null
and so.name like '#Output%'
and not exists (select top 1 null from #ttInclusion where i = so.object_id and lvl <= isnull(@lvl, lvl))
--очищаем список созданных таблиц, которые принадлежат обрабатываемому уровню
delete #ttInclusion
where lvl <= @lvl
end
else
begin
insert #InclusionOutputs(i, tableName, num)
select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
from tempdb.sys.objects so
where so.type = 'U'
and so.name like '#[^#]%'
and object_id('tempdb..' + so.name, 'U') is not null
and so.name like '#Output%'
end
----------------------------------------------------------------------------------------------------
--Выполнение заданий (если его не было - вывод всех #Output)
declare @srcsql varchar(max)
--Выполняем заданный скрипт в util.InclusionRun
if (@p0 is not null and @p0 <> '')
begin
--заменяем псевдонимы @replaceableTableName
if (@p2 is not null and @p2 <> '')
begin
select @p0 = replace(@p0, @p2 + cast(num as varchar(10)), replace(tableName, '#', '#<tokenAfterReplace>'))
from #InclusionOutputs
order by num desc
select @p0 = replace(@p0, '<tokenAfterReplace>', '')
end
--добавляем в скрипт
select @srcsql = isnull(@srcsql + ' ' + char(13), '')
+ @p0 + ' ' + char(13)
end
--Выводим созданные #Output таблицы
if (@p1 is null or @p1 <> '1') --если равно 1, то не выполняем!
begin
--отступ от прошлого скрипта
select @srcsql = isnull(@srcsql + ' ' + char(13), '')
--добавляем в скрипт
select @srcsql = isnull(@srcsql + ' ', '') +
'select * from ' + tableName
from #InclusionOutputs
order by num asc
end
if (@srcsql is not null)
begin
exec (@srcsql)
end
end
go
alter procedure util.InclusionRun
@sql varchar(max), --sql скрипт который выполниться внутри вызываемой процедуры (содержащей util.InclusionEnd)
@notShowOutput bit, --если = 1, то блокировать вывод таблиц начинающихся с #Output
@replaceableTableName varchar(100) = '#Output' -- задать префикс в имени таблиц используемых в @sql, для замены его на соответствующую #Output* таблицу в скрипте.
-- Например, если задать #InclusionOutput, и в процедуре созданы две таблицы #Output55 и #Output0A,
-- то в @sql можно обратиться к #Output55 как к #InclusionOutput1, а к #Output0A как к #InclusionOutput2
as
begin
set nocount on
set ansi_warnings off
if object_id('tempdb..#ttInclusionParameters', 'U') is null
begin
print 'Процедура util.InclusionRun не выполнена, т.к. для неё не созданна таблица #ttInclusionParameters! '
return
end
declare @lvl int
select @lvl = isnull(max(lvl), 0) + 1
from #ttInclusionParameters
insert #ttInclusionParameters(lvl, pr, val)
select @lvl, 0, @sql
union all
select @lvl, 1, '1' where @notShowOutput = 1
union all
select @lvl, 2, @replaceableTableName
end
Другие методы
Можно воспользоваться передачей параметра из функции (
OUTPUT
) и на основе его значения восстановить таблицу. Например, можно передать курсор или XML.На эту тему существует статья.
Использовать курсор для этой задачи я не вижу смысла, только если изначально требуется именно курсор. А вот XML выглядит перспективным. Здесь очень интересные результаты тестов на производительность.
Интересно услышать какие вы используете способы упрощения этой задачи :)
UPD 31.03.2014: Скорректировал пост по идеям из комментариев