Выполнение хранимой процедуры

  • Статья
  • Чтение занимает 5 мин

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)

В этой статье описывается выполнение хранимой процедуры в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

Существует два способа выполнения хранимой процедуры. Первым и наиболее распространенным подходом является вызов процедуры приложением или пользователем. Второй подход — настройка автоматического выполнения процедуры при запуске экземпляра SQL Server . При вызове процедуры приложением или пользователем в вызове явно указывается ключевое слово Transact-SQL EXECUTE или EXEC. Процедура может вызываться и выполняться без ключевого слова EXEC, если процедура является первой инструкцией в пакете Transact-SQL.

Перед началом

ограничения

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

EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

Чтобы показать точные имена системных процедур, запросите представления каталога sys.system_objects и sys.system_parameters .

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

Рекомендации

Выполнение системных хранимых процедур

Имена системных процедур начинаются с префикса sp_. Поскольку они логически отображаются во всех базах данных, определяемых и пользователем, и системой, то они могут выполняться из любой базы данных без полного указания имени процедуры. Однако рекомендуется уточнять имена всех системных процедур указанием схемы sys во избежание конфликтов имен. В следующем примере демонстрируется рекомендуемый метод вызова системной процедуры.

EXEC sys.sp_who;

Выполнение пользовательских хранимых процедур

При выполнении определяемой пользователем процедуры рекомендуется дополнительно указывать имя схемы. Это позволяет немного увеличить производительность, поскольку компоненту Компонент Database Engine не нужно выполнять поиск в нескольких схемах. Также исключается выполнение неправильной процедуры в случае, если в нескольких схемах базы данных имеются процедуры с одним именем.

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

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;
GO

-Или-

EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO

Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine производит поиск процедуры в следующем порядке.

  1. Схема sys текущей базы данных.

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

  3. Схема dbo в текущей базе данных.

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

Процедуры, помеченные для автоматического выполнения, выполняются каждый раз, когда запускается SQL Server и в процессе запуска восстанавливается база данных master. Настройка процедур для автоматического выполнения удобна для операций обслуживания базы данных и для постоянного выполнения процедур в фоновом процессе. Кроме того, автоматический запуск процедур может применяться для выполнения системных или служебных задач в базе данных tempdb, таких как создание глобальной временной таблицы. Это обеспечивает наличие такой временной таблицы при повторном создании базы данных tempdb во время запуска SQL Server.

Автоматически выполняемая процедура работает с теми же разрешениями, что и члены предопределенной роли сервера sysadmin . Любое сообщение об ошибке, сформированное такой процедурой, записывается в журнал ошибок SQL Server .

Ограничений на количество автоматически запускаемых процедур не существует, однако помните, что для выполнения каждой необходим один рабочий поток. Если необходимо выполнить несколько процедур при запуске, которые не должны выполняться параллельно, настройте одну процедуру на автоматический запуск, а вторую вызывайте в ее теле (в конце). Таким образом будет задействован только один рабочий поток.

Совет

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

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

Помечать процедуру для автоматического выполнения может только системный администратор (sa). Кроме того, процедура должна находиться в базе данных master, принадлежать пользователю sa и не иметь входных или выходных параметров.

Используйте процедуру sp_procoption чтобы:

  1. обозначить существующую процедуру как автоматически запускаемую;

  2. отменить выполнение процедуры при запуске SQL Server .

Безопасность

Дополнительные сведения см. в разделах EXECUTE AS (Transact-SQL) и Execute AS Clause (Transact-SQL).

Разрешения

Дополнительные сведения см. в разделе "Разрешения" статьи EXECUTE (Transact-SQL).

Использование среды SQL Server Management Studio

Выполнение хранимой процедуры

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database Engine, разверните его, а затем разверните узел Базы данных.

  2. Разверните нужную базу данных, разверните узлы Программированиеи Хранимые процедуры.

  3. Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру.

  4. В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL.

    Параметр
    Указывает имя параметра.

    Тип данных
    Указывает тип данных параметра.

    Выходной параметр
    Указывает, является ли этот параметр выходным.

    Передать значение NULL
    Передать значение NULL в качестве значения параметра.

    Значение
    Введите значение параметра, передаваемое ему при вызове процедуры.

  5. Чтобы выполнить хранимую процедуру, нажмите кнопку ОК.

Использование Transact-SQL

Выполнение хранимой процедуры

  1. Установите соединение с компонентом Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как выполнить хранимую процедуру, которая принимает один параметр. В примере выполняется хранимая uspGetEmployeeManagers процедура со значением 6, указанным в @EmployeeID качестве параметра .

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

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

Процедуры автозапуска должны находиться в базе данных master и не могут содержать входных (INPUT) или выходных (OUTPUT) параметров. Выполнение хранимых процедур начинается после восстановления всех баз данных и регистрации сообщения «Восстановление завершено» во время начального запуска.

Дополнительные сведения см. в разделе sp_procoption (Transact-SQL).

  1. Установите соединение с компонентом Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы задать автоматическое выполнение процедуры.

EXEC sp_procoption @ProcName = N'<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

Отмена автоматического выполнения процедуры

  1. Установите соединение с компонентом Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы отменить автоматическое выполнение процедуры.

EXEC sp_procoption @ProcName = N'<procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

Дальнейшие действия