Применение выходных параметров

Выходные параметры позволяют хранимой процедуре возвращать данные вызывающей программе. Для определения выходных параметров используется ключевое слово OUT[PUT], которое обязательно как при определении процедуры, так и при ее вызове. В самой хранимой процедуре выходные параметры являются локальными переменными. В вызывающей процедуре или пакете выходные переменные должны быть предварительно определены, чтобы получить результирующие значения. Когда выполнение хранимой процедуры завершается, текущее значение параметра передастся локальной переменной вызывающей программы.

В следующем примере выходной параметр используется для возвращения уникального идентификатора вновь добавленного товара.

CREATE PROCEDURE spr_addProduct

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN

Пример вызова:

DECLARE @IdProd int

EXEC spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

SELECT @IdProd as N'@IdProd'

Обратите внимание на то, что при вызове процедуры переданы значения не для всех параметров. Параметр InStock являются необязательным, поскольку для него указано значение по умолчанию в виде нуля, которое и будет использовано, в случае если для него не будет явно предоставлено другое значение. При этом если бы вызов хранимой процедуры и передача значений происходили с использованием позиционных параметров, то пришлось бы заполнять каждую позицию в списке параметров, по меньшей мере, до того, как встретился бы последний параметр, для которого должно быть предусмотрено значение.

Подтверждение успешного или неудачного завершения работы с помощью возвращаемых значений. Использование команды RETURN.

Любая вызываемая на выполнение хранимая процедура возвращает значение, независимо от того, предусмотрен ли в ней возврат значения или нет. По умолчанию после успешного завершения процедуры СУБД SQL Server автоматически возвращает значение, равное нулю.

Чтобы передать некоторое возвращаемое значение из хранимой процедуры обратно в вызывающий код, достаточно применить оператор RETURN:

RETURN [<Целое число>]

Обратите внимание на то, что возвращаемое значение должно быть обязательно целочисленным.

Возвращаемые значения предназначены исключительно для указания на успешное или неудачное завершение хранимой процедуры и позволяют даже обозначить степень или характер успеха или неудачи. Использование возвращаемого значения для возврата фактических данных, таких как идентификационное значение или данные о количестве строк, затронутых хранимой процедурой, рассматривается как недопустимая практика программирования. Возвращаемое значение 0 указывает на успешное выполнение процедуры и установлено по умолчанию. Компания Microsoft зарезервировала значения от -99 до -1 для служебного пользования. Разработчикам для возвращения состояния ошибки пользователю рекомендуется использовать значения -100 и меньше.

Одной из наиболее важных особенностей оператора RETURN является то, что его выполнение приводит к безусловному завершению работы и выходу из хранимой процедуры. Это означает, что, независимо от местонахождения оператора RETURN в коде хранимой процедуре, после его выполнения больше не будет выполнена ни одна строка кода. Под безусловным завершением работы подразумевается, что действие, предусмотренное оператором RETURN, осуществляется независимо от того, в каком месте кода он обнаруживается. С другой стороны, допускается наличие в коде хранимой процедуры нескольких операторов RETURN, а выполнение этих операторов происходит, только если к этому приводит обычная структура управления процессом выполнения кода.

В предыдущем примере при попытке добавления в таблицу Product информации о новом товаре с дублирующим названием могла произойти ошибка, поскольку по наименованию товара организовано ограничение уникальности. Расширим хранимую процедуру spr_addProduct, предусмотрев предварительную проверку на наличие указанного товара в базе и индикацию об успешности операции через выходной параметр.

ALTER PROCEDURE [dbo].[spr_addProduct]

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

IF EXISTS(SELECT * FROM Product WHERE [Description] = @Description)

RETURN -100

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN 0

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

EXEC @локальная_переменная = имя_хранимой_процедуры;

DECLARE @return_value int,

@IdProd int

EXEC @return_value = spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

IF @return_value = 0

BEGIN

PRINT 'Товар успешно добавлен'

SELECT @IdProd as N'@IdProd'

END

ELSE

BEGIN

PRINT 'При добавлении товара произошла ошибка'

SELECT 'Return Value' = @return_value

END

Задание для самостоятельной работы: Создайте хранимые процедуры, реализующие следующие действия:

· Возврат списка всех заказов содержащих заданный товар (по IdProd).

· Определение количества клиентов, не имеющих ни одного заказа. Результат должен возвращаться через выходной параметр.

· Удаление из базы данных информации об определенном клиенте (по IdCust). Если с данных клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции.

Дата добавления: 2014-12-08