Поделиться через

  • Статья

Выполнение хранимых процедур (компонент Database Engine)

Чтобы выполнить хранимую процедуру, используйте инструкцию Transact-SQL EXECUTE. Также можно выполнить хранимую процедуру без использования ключевого слова EXECUTE, если хранимая процедура является первой инструкцией в пакете.

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

Имена системных хранимых процедур начинаются с символов sp_. Физически они хранятся в Базе данных ресурсов, но логически отображаются в схеме sys каждой системной и пользовательской базы данных в экземпляре SQL Server. Системные хранимые процедуры могут выполняться из любой базы данных, при этом нет необходимости уточнять полное имя хранимой процедуры. Имя без уточнения схемы может быть одночастным, например sp_someproc, или трехчастным, например somedb..sp_someproc, в котором опущена вторая часть (имя схемы).

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

EXEC sys.sp_who;

В нижеследующем примере демонстрируются обратно совместимые методы выполнения системных хранимых процедур.

ПримечаниеПримечание

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

 
EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;

Выявление соответствия параметров сортировки базы данных

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

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

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

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

Имена системных расширенных хранимых процедур начинаются с символов xp_. Физически они хранятся в Базе данных ресурсов, но логически отображаются в схеме sys каждой системной и пользовательской базы данных в экземпляре SQL Server. В нижеследующем примере демонстрируется рекомендуемый метод выполнения системной расширенной хранимой процедуры.

EXEC sys.xp_subdirs 'c:\';

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

При выполнении пользовательской хранимой процедуры (в пакете или внутри модуля пользовательской хранимой процедуры или функции) настоятельно рекомендуется уточнять имя хранимой процедуры указанием, по крайней мере, имени схемы.

В нижеследующем примере демонстрируется рекомендуемый метод выполнения пользовательской хранимой процедуры.

USE AdventureWorks2008R2;
GO
EXEC dbo.uspGetEmployeeManagers 50;

-или-

EXEC AdventureWorks2008R2.dbo.uspGetEmployeeManagers 50;
GO

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

  • схема sys текущей базы данных;

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

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

Важное примечаниеВажно!

Если какая-либо созданная пользователем хранимая процедура имеет то же имя, что и системная хранимая процедура, пользовательская хранимая процедура ни в коем случае не будет выполнена при использовании неуточненного имени без указания имени схемы. Дополнительные сведения см. в разделе Создание хранимых процедур (компонент Database Engine).

Указание аргументов

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

Предоставленное значение должно быть константой или переменной; нельзя указывать имя функции в качестве значения аргумента. Переменные могут быть пользовательскими или системными, например @@spid.

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

USE AdventureWorks2008R2;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

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

Чтобы указать параметр, возвращающий значение вызываемой программы, используйте ключевое слово OUTPUT. Дополнительные сведения см. в разделе Указание направления параметра.

Указание порядка аргументов

При указании аргументов в виде @аргумент = value можно указывать их в любом порядке. Можно также опустить аргументы, для которых представлены значения по умолчанию. При указании одного аргумента в виде @аргумент = value необходимо предоставить все последующие аргументы тем же способом. Если аргументы указываются не в формате @аргумент = value, следует указывать их в порядке, определенном для инструкции CREATE PROCEDURE.

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

Использование значений по умолчанию для аргументов

Хотя разрешается опустить аргументы, для которых предоставлены значения по умолчанию, можно лишь подвергнуть усечению список аргументов. Например, когда в хранимой процедуре присутствует пять аргументов, можно опустить четвертый и пятый, но нельзя пропустить четвертый и включить пятый, если только аргументы не были предоставлены в формате @аргумент = value.

Значение по умолчанию, определенное для аргумента в хранимой процедуре, используется, когда:

  • не указано значение для аргумента при выполнении хранимой процедуры;

  • в качестве значения для аргумента указывается ключевое слово DEFAULT.