Выполнение процедуры

Создание процедуры средствами Transact-SQL

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

CREATE PROCEDURE|PROC <sproc name>

[<parameter name> [<schema>.]<data type> [VARYING]

[= <default value>] [OUT[PUT]] [READONLY]

[, n...]

[WITH

RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|’<user name>’}]

[FOR REPLICATION]

AS

<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

· Имя процедуры должно удовлетворять правилам именования объектов MS SQL Server;

· parameter name определяет имя параметра (должно начинаться с символа @), который будет использоваться для передачи входных или выходных данных (при указании ключевого слова OUTPUT);

· data type указывает, к какому типу данных должны относиться значения параметра;

· default value – позволяет определить значение по умолчанию, если при вызове процедуры параметр был не указан;

· опция READONLY создает параметр доступный только для чтения, если параметр имеет тип table, то указание READONLY обязательно;

· режим WITH ENCRYPTION запрещает дальнейший просмотр кода создаваемой хранимой процедуры, шифруя его;

· режим RECOMPILE указывает, что сервер не кэширует план исполнения процедуры, и процедура компилируется только во время выполнения.

После ключевогослова AS следуют или команды Transact-SQL, которые и составляют тело процедуры, или прописывается метод из указанной сборки .Net Framework.

Пример создания хранимой процедуры с шифрованием:

CREATE PROCEDURE HumanResources.uspEncryptThis

WITH ENCRYPTION

AS

SELECT BusinessEntityID, JobTitle, NationalIDNumber,

VacationHours, SickLeaveHours

FROM HumanResources.Employee;

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

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Результатом выполнения будет сообщение:

The text for object 'HumanResources.uspEncryptThis' is encrypted

(Текст объекта 'HumanResources.uspEncryptThis' зашифрован).

Хранимая процедура может быть выполнена с помощью оператора EXECUTE:

EXEC[UTE]

[@СтатусВозврата =] ИмяПроцедуры

[ [@параметр=] {Значение | Выражение} [OUTPUT] ] [,...]

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

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