Модификация хранимой процедуры

Управление автоматическим выполнением хранимых процедур

Вы можете предписать системе инициировать выполнение хранимой процедуры ав­томатически при каждом запуске SQL Server. Зачастую подобный подход использу­ется для осуществления различных административных задач. Например, можно на­писать процедуру, которая при старте системы производит проверку целостности базы данных, а затем осуществляет резервное копирование этой базы данных. Ино­гда автоматически запускаемые хранимые процедуры используют для создания временных таблиц в базе данных tempdb. Это гарантирует, что при каждом запуске SQL Server вы будете получать одинаковый набор временных таблиц.

Определить для хранимой процедуры возможность автоматического запуска могут только участники стандартных ролей сервера sysadmin и setupadmin. Для этой цели используется специальная хранимая процедура

sp_procoptioir[[(aprocName =] 'procedure']

Параметр procedure определяет имя хранимой процедуры, для которой необ­ходимо сконфигурировать автоматический запуск. При этом в процессе выпол­нения данная процедура будет обладать тем же набором прав, что и участник стандартной роли сервера sysadmin.

Каждая автоматически запущенная процедура выполняется в контексте от­дельного соединения с сервером. Поэтому чем больше подобных хранимых процедур запущено, тем меньше соединений остается для пользователей. Чтобы свести к минимуму число соединений, задействованных для обслуживания авто­матически выполняемых хранимых процедур, Microsoft рекомендует построить процесс запуска процедур по следующей схеме. При старте сервера запускается всего одна хранимая процедура, которая затем вызывает другую. Таким образом, для выполнения процедур требуется всего лишь одно соединение. При этом, однако, необходимо следить за тем, чтобы не превысить допустимый уровень вложенности хранимых процедур, равный 32. При попытке превысить этот уровень система выдает сообщение об ошибке. Получить информацию о степени вложенности можно при помощи функции @@NESTLEVEL.

Одним из достоинств хранимых процедур является возможность их многократной модификации без необходимости изменения кода использующих их приложений. Наиболее удобный способ изменения кода хранимой процедуры предоставляет утилита Enterprise Manager. Для этого достаточно дважды щелкнуть на требуемой процедуре и в появившемся окне отредактировать ее код. Когда все изменения будут сделаны, достаточно нажать на кнопку Check Syn­tax и, если проверка синтаксиса пройдет успешно, подтвердить изменения.

Чтобы модифицировать хранимую процедуру только средствами Transact-SQL, используйте следующую команду:

ALTER PROCEDURE procedure_name [;number]

[ {©parameter data_type } [VARYING] [= default] [OUTPUT] ] [,...n]

[WITH

{ RECOMPILE

| ENCRYPTION

| RECOMPILE. ENCRYPTION} ]

[FOR REPLICATION]

AS

sql_statement [...n]

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

Дата добавления: 2015-05-09