[Из песочницы] Передача табличных данных из хранимой процедуры

Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL. Не берусь утверждать, что данные методы самые эффективные. Это всего лишь то, что я использую в своей работе. Всё это разрабатывалось под Microsoft SQL Server 2008 R2, но, думаю, должно работать и под 2005.

Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.



Пусть процедура, из которой нам нужно получить данные будет такой:

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(p1, p2)
exec Proc1
select * from #t1

Плюсы и минусы:

• Передаваемые поля перечисляются 3 раза (это внутренний 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-3 раза, и еще по одному перечислению на каждое новое использование


• Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от 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 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-3 раза, при этом каждое новое использование не добавляет сложности


• Для непосредственного вывода результата также требуются дополнительные действия


• Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции alter table #t1, и процесс ожидает полного завершения Proc1 (не Proc1_AlterTable!) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)

4 Метод

Модификация третьего, избавляемся от блокировок. Для этого сразу создаем таблицу с помощью табличной функции.

create procedure Proc1
as
begin
        insert #t1(p1, p2) 
        select 1 p1, 'b' p2
end
go
create function Proc1_AlterTable()
returns table
as
return
(
        select cast(null as int) p1, cast(null as varchar(max)) p2
        where 1=2
)
go
-- используем:
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
select * 
into #t1
from Proc1_AlterTable()
exec Proc1
select * from #t1

Плюсы и минусы:

• Передаваемые поля перечисляются 2-3 раза, при этом каждое новое использование не добавляет сложности


• Для организации непосредственного вывода результата также требуются дополнительные действия


• Есть небольшие сложности с созданием индексов и ограничений, т.к. их мы уже не можем поместить в 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 выглядит перспективным. Здесь очень интересные результаты тестов на производительность.

Интересно услышать какие вы используете способы упрощения этой задачи.

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.