Согласно определению хранимая процедура в результате своего выполнения значения не возвращает, но это не совсем так.
На самом деле из хранимой процедуры можно получить значения и для этого существует целых три способа:
- Выходные параметры;
- Использование кодов возврата;
- Использование сочетания операторов INSERT … EXEC.
Использование выходных параметров
Это самый простой способ.
Чтобы им воспользоваться достаточно объявить один из параметров с ключевым словом OUTPUT и присвоить ему значение внутри процедуры.
Ниже приведён пример хранимой процедуры, которая вычисляет количество записей в таблице и возвращает полученный результат в выходном параметре.
1 2 3 4 5 6 7 |
CREATE PROCEDURE [dbo].[Test] @countRecords int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @countRecords=COUNT(*) FROM [dbo].[Mans]; END |
Чтобы получить данные из выходного параметра объявим переменную и передадим её в качестве параметра процедуры, который был объявлен выходным.
1 2 |
DECLARE @resultValue int EXEC @return_value = [dbo].[Test] @countRecords=@resultValue OUTPUT; |
Обратите внимание, что при передаче переменной в выходной параметр также требуется ключевое слово OUTPUT.
Коды возврата
Коды возврата подходят для быстрого определения корректности результатов выполнения процедуры.
По умолчанию коды возврата уже встроены в хранимую процедуру и при её успешном выполнении она возвращает значение «0». Но, разработчик может определить и собственные коды возврата.
Например, пусть для хранимой процедуры из рассмотренного выше примера будут определены следующие значения кодов возврата:
- 0 — процедура выполнена успешно и получен корректный результат (не будем нарушать «традицию» и обозначим полностью успешное выполнение этим кодом);
- 1 — процедура выполнена успешно, но результат некорректный.
В данном случае корректным пусть будет считаться результат выполнения при котором в выходной параметр было передано число больше нуля.
С поддержкой кодов возврата определённых разработчиком наша хранимая процедура примет следующий вид:
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE [dbo].[Test] @countRecords int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @countRecords=COUNT(*) FROM [dbo].[Mans]; IF @countRecords>0 RETURN (0) ELSE RETURN(1); END |
Получить код возврата можно при помощи обычного присвоения в операторе EXEC.
1 2 |
DECLARE @returnCode int; EXEC @returnCode = [dbo].[Test] @countRecords=@resultValue OUTPUT; |
Использование сочетания операторов INSERT … EXEC
Этот способ подходит для получения из хранимой процедуры табличных значений.
Допустим у нас имеется следующая хранимая процедура, которая осуществляет выборку данных из таблицы, которая содержит всего два поля: идентификатор (int) и некоторый текст (nvarchar(max)).
1 2 3 4 5 6 |
CREATE PROCEDURE [dbo].[Test] AS BEGIN SET NOCOUNT ON; SELECT * FROM [dbo].[TestTable]; END |
Как получить результаты этой выборки вне процедуры?
Для этого вначале создадим временную таблицу по своей структуре аналогичную исходной:
1 |
CREATE TABLE #testTempTable (id int, Txt nvarchar(max)); |
После этого добавим в неё строки при помощи оператора INSERT. Только в нашем случае вместо перечня полей и их значений будет вызов хранимой процедуры.
1 |
INSERT INTO #testTempTable EXEC [dbo].[Test]; |
В результате наша временная таблица будет заполнена данными из хранимой процедуры. Аналогичным образом можно добавлять данные из хранимых процедур в постоянные таблицы.
Когда стоит и когда не стоит возвращать значения из хранимых процедур
Рассмотренные в статье способы получения данных из хранимых процедур позволяют решать с их помощью задачи практически любой степени сложности. Однако всё же не рекомендуется злоупотреблять и сводить всё к хранимым процедурам.
Во-первых для того чтобы получить какое-либо значение в виде скалярной величины и даже таблицы, в Transact-SQL существует специальная категория объектов называемая функциями.
Использование функций для получения любых значений гораздо эффективнее процедур.
Во-вторых, вследствие наличия вышеупомянутых функций возврат значения из хранимой процедуры требуется в основном для проверки кода возврата или выполнения задач по администрированию.
О последнем стоит сказать особо так как многие системные хранимые процедуры содержат различные выборки и рассмотренные нами методы позволяют получить доступ к их результатам для последующего анализа.
Во всех остальных случаях лучше использовать функции.
Добавить комментарий