Создание хранимых процедур
Как вы можете догадаться, хранимые процедуры создаются с использованием одной из разновидностей оператора CREATE – на этот раз, CREATE PROCEDURE. Синтаксис оператора CREATE PROCEDURE следующий:
CREATE PROCEDURE имя_процедуры [список_параметров] AS операторы_процедуры
Имя_процедуры должно отвечать правилам, принятым для идентификаторов.
Совет. Вы можете создать временную локальную или глобальную хранимую процедуру, указав перед именем процедуры # или ## соответственно.
Операторы_процедуры, следующие после ключевого слова AS в операторе CREATE, определяют действия, которые будут выполняться при вызове хранимой процедуры. Они по своему функциональному назначению полностью аналогичны сценариям. Фактически можно считать все, что находится перед ключевым словом AS, заголовком SQL-сценария.
Хранимые процедуры могут вызвать другие хранимые процедуры, т. е. реализовывать вложенность. Фактическая глубина вложенности хранимых процедур составляет 32.
Создайте простую хранимую процедуру
- Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
- Выделите сценарий SimpleSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст хранимую процедуру.
- Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
- Введите в окне вкладки Editor (Редактор) следующий оператор:
EXECUTE SimpleSP
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты.
- Закройте окно Query (Запрос), не сохраняя изменения при появлении соответствующего окна-запроса.
Каждый из параметров в списке_параметров имеет следующую структуру:
@имя_параметра тип_данных [= значение_по_умолчанию] [OUTPUT]
Имя_параметра должно удовлетворять правилам, принятым для идентификаторов. Имена параметров должны начинаться с символа @, подобно локальным переменным. Параметры являются локальными переменными; они видимы только в пределах хранимой процедуры. В одной хранимой процедуре может быть использовано максимально 2100 параметров.
Значение_по_умолчанию представляет собой значение, которое будет использоваться хранимой процедурой в случае, если пользователь не укажет значение для входного параметра в вызове хранимой процедуры. Ключевое слово OUTPUT, которое также не является обязательным, определяет параметры, которые будут возвращены в вызвавший процедуру сценарий.
Создайте хранимую процедуру с входным параметром
- Перейдите к окну, содержащему сценарий SimpleSP.
- Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
- Выделите сценарий InputSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст хранимую процедуру.
- Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
- Введите на вкладке Editor (Редактирование) следующий оператор:
EXECUTE InputSP 'Basil' - Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты.
- Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Создайте хранимую процедуру со значением по умолчанию
- Перейдите к окну, содержащему сценарий InputSP.
- Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
- Выделите сценарий DefaultSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст хранимую процедуру.
- Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
- Введите на вкладке Editor (Редактирование) следующий оператор:
EXECUTE DefaultSP - Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты.
- Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Создайте хранимую процедуру с выходным параметром
- Перейдите к окну, содержащему сценарий DefaultSP.
- Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
- Выделите сценарий OutputSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст хранимую процедуру.
- Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
- Введите следующие операторы на вкладке Editor (Редактор):
DECLARE @myOutput char(6) EXECUTE OutputSP @myOutput OUTPUT SELECT @myOutput - Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты.
- Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Возврат значений реализуется с помощью оператора RETURN, который имеет следующую форму:
RETURN(int)
В операторе RETURN int – это целочисленное значение. Как мы видели раньше, возврат значений чаще всего используется для определения статуса выполнения хранимой процедуры. При этом 0 указывает на успешное завершение выполнения, а любое другое число указывает на ошибку. Ошибки могут быть проанализированы с помощью глобальной переменной @@ERROR, которая возвращает статус выполнения последней команды Transact-SQL: 0 указывает на успешное выполнение, а ненулевое значение указывает, что имела место ошибка.
Совет. Строка сообщения об ошибках SQL Server хранится в главной базе данных в таблице sysmessage. Вы можете добавить свои собственные сообщения об ошибках в эту таблицу, воспользовавшись системной процедурой sp_addmessage, а затем применить функцию RAISERROR для генерирования характерных для базы данных, или даже для приложения, ошибок.
Создайте хранимую процедуру с возвращаемым значением
- Перейдите к окну, содержащему сценарий OutputSP.
- Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
- Выделите сценарий ErrorSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
- Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст хранимую процедуру.
- Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
- Введите следующие операторы на вкладке Editor (Редактор):
DECLARE @theError int EXECUTE @theError = ErrorSP SELECT @theError AS 'Return Value' - Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты. Во второй панели сетки отображается 0, что указывает на успешное выполнение команды.
- Закройте окно Query (Запрос), без сохранения изменений.
