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

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

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

CREATE PROCEDURE spr_getOrders

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate >= '01.01.2010')

RETURN

Чтобы протестировать новую процедуру, откройте новый запрос SQL Server и выполните следующий код.

EXEC spr_getOrders

Команда EXECUTE или сокращенно EXEC выполняет указанную хранимую процедуру.

В данном случае хранимая процедура вернет все строки из таблицы Order, в которых значение поля OrdDate больше 1 января 2010 года, в соответствии с содержащимся в нем запросом на выборку.

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

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

@parameter_name [AS] datatype [= default|NULL]

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

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

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

ALTER PROCEDURE [dbo].[spr_getOrders]

@dateBegin datetime,

@dateEnd datetime

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd)

RETURN

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

В следующих трех примерах продемонстрированы вызовы хранимых процедур и передача им параметров с использованием исходного порядка и имен:

EXEC spr_getOrders '01.01.2010', '01.07.2010'

EXEC spr_getOrders

@dateBegin = '01.01.2010',

@dateEnd = '01.07.2010'

EXEC spr_getOrders '01.01.2010', @dateEnd = '01.07.2010'

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

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