Выходной параметр противоположен входному. С помощью входного параметра вы задаете хранимой процедуре значение для работы; с помощью выходного параметра хранимая процедура возвращает значение, используемое в дальнейших запросах. Выходной параметр создается в том же месте, где и входной — между именем процедуры и ключевым словом AS. Единственное отличие состоит в том, что выходной параметр определяется с помощью ключевого слова OUTPUT. В следующем примере мы создадим простую хранимую процедуру калькулятора, чтобы увидеть возможности выходного параметра.
1. Откройте SQL Server Management Studio. В окне Object Explorer разверните папки сервера Databases=> Sales=> Programmability.
2. Щелкните правой кнопкой на папке Stored Procedures и выберите команду New Stored Procedure. Откроется редактор запросов SQL Server с шаблоном запроса на создание хранимой процедуры.
3. В предложении CREATE PROCEDURE введите имя процедуры: Calc.
4. В секции Add the parameters for the stored procedure here для создания первого входного параметра используйте следующие данные:
• Parameter Name: @ first
• Datatype: int
• Value_Default:: 0
5. Для создания второго входного параметра используйте следующие данные:
• Parameter Name: @sec
• Datatype: int
• Value_Default:: 0
6. Для создания выходного параметра используйте следующие данные:
• Parameter Name: @ret
• Datatype: int
• Value_Default:: 0
12. В блок синтаксиса Transact-SQL введите следующий код (рис. 4):
SET @ret = @first + @sec
13. Чтобы сохранить процедуру, щелкните на кнопке Save панели инструментов.
Рис.4. Формирование хранимой процедуры-операции
Теперь нам нужно протестировать процедуру. Чтобы получить выходной параметр хранимой процедуры, его следует куда-то поместить. Это значит, что при выполнении запроса вы должны задать оба входных параметра, а также определить место хранения выходного.
1. Чтобы протестировать изменения, откройте новый запрос SQL Server и выполните следующий код (обратите внимание на то, что переменная @answer специальнопредназначена для хранения результата, возвращаемого выходным параметром @ret хранимой процедуры) (рис. 5):
USE Sales
DECLARE @answer int
EXEC dbo.Calc 1, 2, @answer OUTPUT
SELECT 'The answer is:', @answer
2. Закройте окно запроса.
Так что же все-таки произошло? Мы создали параметр @ret для возвращения значения программе, вызвавшей процедуру. Затем перед выполнением хранимой процедуры мы создали с помощью объявления DECLARE @answer переменную для хранения выходного параметра (ключевое слово DECLARE используется для создания временных переменных). После создания переменной мы выполнили хранимую процедуру и указали ей поместить значение @ret во временную переменную @answer, отображаемую впоследствии на экране с помощью инструкции SELECT.
Рис.5. Мы получили результат сложения
Выводы
Хранимая процедура представляет собой набор инструкций Transact-SQL (обычно запросов), хранящийся на сервере в ожидании запуска пользователями. Основное достоинство централизованного хранения заключается в том, что при выполнении пользователям не нужно пересылать по сети сотни строк кода, а всего лишь одну строку EXEC xpaнимая_процедура. Этими хранимыми процедурами легче управлять, чем распределенным кодом, поскольку, если вам потребуется изменить код процедуры, то сделать это нужно только на сервере, а не на всех машинах клиентов.
Хранимые процедуры используются для извлечения и изменения данных в любое время.
В отличие от триггеров, хранимая процедура явно вызывается приложением. Системные хранимые процедуры очень полезны для администрирования и поддержки базы данных.
Пользовательские хранимые процедуры применяются практически в любых задачах. Их преимущества, по сравнению с обычными представлениями и запросами, более чем очевидны: после первого выполнения компилированный план процедуры хранится в быстродействующем кэше в оперативной памяти, что существенно повышает скорость выполнения запросов. Другое их преимущество состоит в том, что пользователь может получить право выполнения хранимой процедуры, даже если он не имеет права доступа к тем объектам, к которым обращается процедура.
