Параметризация

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

При вызове хранимой процедуры параметры могут быть заданы либо с учетом позиции, либо по имени. В самой вызываемой хранимой процедуре способ, применяемый для передачи параметров, не играет особой роли, поскольку для всех параметров, независимо от способа их передачи в процедуру, используется одинаковый формат объявления.

Объявление формальных параметров

Для объявления параметра необходимо задать от двух до четырех фрагментов информации:

имя;

тип данных.

заданное по умолчанию значение;

обозначение выходного параметра.

При этом применяется следующий синтаксис: @parameter name [AS] data type [= defaultNULL] [VARYING] [OUTPUTOUT]

Правила именования параметров по существу совпадают с правилами именования переменных. На типы данных распространяются также правила, применяемые к переменным.

Одно особое требование, которое следует учитывать при объявлении типа данных, состоит в тюм, чтю при объявлении параметра типа CURSOR необходимо июпользовать опции VARYING и OUTPUT. Формальный параметр этюго типа используется довольно редко, но следует помнить, каковы условия вызова хранимой процедуры с этим параметром.

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

няемое по умолчанию, не задается, то формальный параметр считается обязательным, поэтому при вызове хранимой процедуры должно быть задано начальное значение, так как в противном случае активизируется ошибка. Чтобы задать предусмотренное по умолчанию значение, необходимо добавить знак равенства (=) после обозначения типа данных, а затем установить значение, применяемое по умолчанию. Благодаря этому пользователи получают возможность при вызове хранимой процедуры принимать решение о том, следует ли задать другое значение параметра или воспользоваться значением, предусмотренным по умолчанию.

Так, например, если бы было решено выборочно ограничивать список, формируемый по условию предыдущего примера, только теми служащими, фамилии которых начинаются с букв, принадлежащих к заданному множеству, то такую задачу можно было бы выполнить, предусмотрев передачу в процедуру параметра LastName:

ALTER PROC spEmployee

©LastName nvarchar(50) = NULL

IF ©LastName IS NULL

SELECT * FROM HumanResources.Employee

ELSE

SELECT c.LastName, c.FirstName, е.*

FROM Person.Contact с

JOIN HumanResources.Employee e

ON e.ContactID = e.ContactID WHERE c.LastName LIKE ©LastName + %

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

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

Иногда возникает необходимость передать в тот объект, из которого вызвана хранимая процедура, не набор строк, а какую-то другую информацию.

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

Для более наглядной иллюстрации данной ситуации воспользуемся хранимой процедурой uspLogError, которая уже имеется в базе данньгх AdventureWorks. Эта хранимая процедура выглядит примерно так:

-- Процедура uspLogError регистрирует в таблице ErrorLog информацию об ошибке, -- после возникновения которой управление было передано блоку CATCH конструкции -- TRY...CATCH. Эти действия должны быть выполнены в области определения блока -- CATCH, так как в противном случае произойдет возврат без вставки информации --об ошибке

CREATE PROCEDURE [dbo].[uspLogError]

©ErrorLogID [int] = 0 OUTPUT -- Содержит значение идентификатора ErrorLogID

-- строки, вставленной процедурой uspLogError

-- в таблицу ErrorLog

BEGIN

SET NOCOUNT ON;

-- Значение выходного параметра, равное О, указывает на то, что запись -- в журнал информации об ошибке не выполнена SET ©ErrorLogID = 0;

BEGIN TRY

-- Если информация об ошибке, которая могла бы быть записана в журнал, -- отсутствует, то происходит возврат IF ERROR NUMBER{) IS NULL RETURN;

-- Выполнить возврат, если выполняемая транзакция еще не зафиксирована.

-- Вставка или модификация данных не допускается, если транзакция

-- находится в незафиксированном состоянии

IF XACT STATE{) = -1

BEGIN

PRINT Cannot log error since the current transaction is in an uncommittable state.

+ Rollback the transaction before executing uspLogError in order to successfully log error information. ;

RETURN;

INSERT [dbo].[ErrorLog] {

[UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage] )

VALUES {

CONVERT{sysname, CURRENT USER), ERROR NUMBER{), ERROR SEVERITY{), ERROR STATE{), ERROR PROCEDURE{), ERROR LINE{), ERROR MESSAGE{) ) ;

Возвратить значение ErrorLogID вставленной строки SET ©ErrorLogID = ©©IDENTITY; END TRY BEGIN CATCH

PRINT An error occurred in stored procedure uspLogError: ; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH

END;

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