Передача табличних даних з збереженої процедури

Мова піде про методи отримання результатів роботи процедури у вигляді таблиць для подальшої роботи з ними в 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 виглядає перспективним. Тут дуже цікаві результати тестів на продуктивність.
Цікаво почути якісь ви використовуєте способи спрощення цього завдання.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.