Create ms sql procedure: CREATE PROCEDURE (Transact-SQL) — SQL Server

Передача табличных данных из хранимой процедуры / Хабр

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

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


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

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. #]%'
			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. #]%'
			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: Скорректировал пост по идеям из комментариев

Создать хранимую процедуру в SQL Server 2017

Преимущество Объяснение
Модульное программирование Вы можете написать хранимую процедуру один раз, а затем вызывать ее снова и снова из разных частей приложения (и даже из нескольких приложений).
Производительность Хранимые процедуры обеспечивают более быстрое выполнение кода и сокращают сетевой трафик.

  • Более быстрое выполнение: хранимые процедуры анализируются и оптимизируются сразу после их создания, а хранимая процедура сохраняется в памяти. Это означает, что он будет выполняться намного быстрее, чем отправка многих строк кода SQL из вашего приложения на SQL Server. Для этого SQL Server должен компилировать и оптимизировать код SQL при каждом запуске.
  • Уменьшенный сетевой трафик: если вы отправляете много строк кода SQL по сети на свой SQL Server, это повлияет на производительность сети. Это особенно верно, если у вас есть сотни строк кода SQL и/или вы много работаете с вашим приложением. Выполнение кода на сервере SQL (в виде хранимой процедуры) избавляет от необходимости отправлять этот код по сети. Единственным сетевым трафиком будут предоставленные параметры и результаты любого запроса.
Безопасность Пользователи могут выполнять хранимую процедуру без необходимости выполнять какие-либо операторы напрямую. Таким образом, хранимая процедура может предоставлять расширенные функциональные возможности базы данных для пользователей, которые обычно не имеют доступа к этим задачам, но доступ к этим функциональным возможностям строго контролируется.