Параметры хранимой процедуры

У хранимой процедуры может быть до 1024 параметров. Определение каждого параметра имеет такую форму:

@имя-параметра тип-данных

Имя параметра должно начинаться с символа @, за которым могут следовать любые буквы Unicode, цифры и символы @, $, # и _. Никогда не начитайте имена параметров с символов @@, поскольку этот префикс SQL Server использует для некоторых своих встроенных функций. Для параметров допускаются те же типы данных, что и для столбцов таблиц, а также особый тип данных cursor, о котором мы поговорим позднее. Если у процедуры несколько параметров, разделите их определения запятыми.

Для параметра можно задать значение по умолчанию, которое будет присваиваться ему в том случае, если значение этого параметра не задано в вызове процедуры. Вот как можно модифицировать предыдущий пример, чтобы по умолчанию процедура ListCustWithDiscount возвращала все строки со скидкой, большей 0,001:

CREATE PROCEDURE ListCustWithDiscount

@MinDiscount DEC(5,3) = 0.001

AS SELECT *

FROM Customer

WHERE Discount >= @MinDiscount

Эту процедуру можно вызвать вовсе без аргументов, что будет равнозначно вызову ее с аргументом 0,001:

EXECUTE ListCustWithDiscount

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

CREATE PROCEDURE GetCustDiscount @CustId INT,

@Discount DEC(5,3) OUTPUT

AS SET @Discount =

(SELECT Discount FROM Customer WHERE Custld = @CustId)

Обратите внимание на то, как в этом примере используется скалярный запрос: возвращаемое им значение присваивается выходному параметру процедуры @Discount. Когда процедура завершает свою работу, вызывающей процедуре или приложению возвращаются последние значения, присвоенные ее выходным параметрам. Для параметров хранимых процедур допускаются значения NULL. Это значит, что и в процедуру, и из процедуры можно передавать значения null.

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

EXECUTE GetCustDiscount 123789, @CustDiscount OUTPUT

Вместо аргумента, для которого в процедуре определено значение по умолчанию, в инструкции execute можно использовать ключевое слово DEFAULT. В конце списка аргументов такие аргументы можно просто опустить.

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

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

EXECUTE GetCustDiscount @CustId = 123789,

@Discount = @CustDiscount OUTPUT

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

EXECUTE GetCustDiscount

@Discount = @CustDiscount OUTPUT, @CustId = 123789

Transact-SQL допускает даже смешанный способ вызова параметров: первые не­сколько аргументов можно задать позиционно, а остальные в любом порядке по именам. Но учтите, что вслед за параметром, заданным по имени, позиционно задать остальные параметры невозможно, их тоже нужно будет задать по именам.

Результирующие наборы строк

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

CREATE PROCEDURE ListLowHighDiscCust AS

SELECT * FROM Customer WHERE Discount < .01

SELECT * FROM Customer WHERE Discount > .1

Возвращаемый код состояния

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

CREATE PROCEDURE ListCustWithDiscount

@MinDiscount DEC(5,3) = 0.001

AS IF (@MinDiscount > 1.0) RETURN (1)

SELECT *

FROM Customer

WHERE Discount >= @MinDiscount

RETURN (0)

Возвращаемое значение не является обязательным. Им может быть любое цело­численное выражение. Для его получения в вызывающей процедуре используется опе­ратор присваивания:

EXECUTE @Status = ListCustWithDiscount 0.1

А можно так:

EXECUTE @Status = ListCustWithDiscount @MinDiscount=0.1

Примечание. Для своих системных процедур SQL Server интерпретирует значение 0 как успешный вызов, а значения от -1 до -99 как системные коды ошибок (например, ошибка преобразования данных). В пользовательских процедурах можно тоже использовать значение 0 как индикатор успешного вызова, но не стоит возвращать значения от -1 до -99.

Задание к работе

Создать хранимые процедуры и показать в клиентском приложении результаты их выполнения.