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

Статья
12/15/2008

Для создания хранимых процедур служит инструкция Transact-SQL CREATE PROCEDURE.

Перед созданием хранимой процедуры обратите внимание на следующее.

Инструкцию CREATE PROCEDURE нельзя использовать вместе с другими инструкциями SQL в одном пакете.
Для создания хранимых процедур требуется разрешение CREATE PROCEDURE в базе данных и разрешение ALTER в соответствующей схеме. Для создания хранимых процедур CLR необходимо или владеть сборкой, на которую ссылается <method_specifier>, или иметь в этой сборке разрешение REFERENCES.
Хранимые процедуры являются объектами схемы, и их имена должны удовлетворять требованиям к именам идентификаторов.
Хранимую процедуру можно создать только в текущей базе данных.

При создании хранимой процедуры необходимо указать следующее.

Входные и выходные параметры в вызывающей процедуре или пакете.
Инструкции, которые выполняют операции над базой данных, включая вызовы других процедур.
Код состояния, который возвращается вызывающей процедуре или пакету и обозначает успешное или неудачное выполнение (и причину ошибки).
Инструкции, которые требуются для обнаружения и обработки потенциальных ошибок.
В Microsoft SQL Server 2005 появились такие функций обработки ошибок, как ERROR_LINE и ERROR_PROCEDURE, которые можно использовать в хранимых процедурах. Дополнительные сведения см. в разделе Использование конструкции TRY...CATCH в Transact-SQL.

Присвоение имен хранимым процедурам

Настоятельно не рекомендуется присваивать хранимым процедурам имена с префиксом sp_. SQL Server использует префикс sp_ для обозначения системных хранимых процедур. Выбранное имя может конфликтовать с системными процедурами, которые появятся в будущем. Приложение, которое ссылается на процедуру, указанную не полностью (без схемы), имя которой совпадает с именем системной хранимой процедуры, будет работать неправильно, поскольку вместо нужной процедуры будет выполнена системная хранимая процедура.

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

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Кроме того, применение явного квалификатора схемы улучшает производительность. Разрешение имен выполняется несколько быстрее, если компоненту Database Engine не приходится искать процедуру в нескольких схемах. Дополнительные сведения см. в разделе Выполнение хранимых процедур.

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

При создании личных и глобальных временных хранимых процедур, по аналогии с временными таблицами используются префиксы # и ##. Префикс # обозначает локальную временную процедуру, а ## — глобальную. Если завершить работу SQL Server, эти процедуры будут удалены.

Временные хранимые процедуры полезно использовать при подключении к предыдущим версиям SQL Server, которые не поддерживают повторное использование планов выполнения инструкций и пакетов Transact-SQL. Приложения, подключающиеся к SQL Server 2000 или более поздней версии, вместо временных хранимых процедур используют системную процедуру sp_executesql. Локальную временную процедуру может выполнить только соединение, которое ее создало, процедура автоматически удаляется при завершении соединения.

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

Хранимая процедура, созданная непосредственно в базе данных tempdb без префиксов # и ##, автоматически удаляется при завершении работы SQL Server, поскольку база данных tempdb создается заново при каждом запуске SQL Server. Процедуры, созданные непосредственно в базе данных tempdb, существуют даже после закрытия соединения, в котором они были созданы.

Примечание.
Интенсивное использование временных хранимых процедур может привести к конкуренции на доступ к системным таблицам в базе данных tempdb и значительно снизить производительность. Вместо временных процедур рекомендуется использовать процедуру sp_executesql. Хранимая процедура sp_executesql не хранит данные в системных таблицах и позволяет избежать конкуренции.

Интенсивное использование временных хранимых процедур может привести к конкуренции на доступ к системным таблицам в базе данных tempdb и значительно снизить производительность. Вместо временных процедур рекомендуется использовать процедуру sp_executesql. Хранимая процедура sp_executesql не хранит данные в системных таблицах и позволяет избежать конкуренции.

Хранимые процедуры среды CLR не могут быть временными.

Примеры

А. Использование простой процедуры в сложной инструкции SELECT

Следующая хранимая процедура возвращает сведения обо всех сотрудниках (имя, фамилию, должность и подразделение) из представления. Данная хранимая процедура не имеет параметров.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

Хранимую процедуру uspGetEmployees можно выполнить следующим образом.

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

Б. Использование простой процедуры с параметрами

Следующая хранимая процедура возвращает сведения об указанном (по имени и фамилии) сотруднике из представления. Имя и фамилия, переданные в хранимую процедуру, должны полностью совпадать с данными искомого сотрудника.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

Хранимую процедуру uspGetEmployees можно выполнить следующим образом.

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

В. Использование простой процедуры с подстановочными параметрами

Следующая хранимая процедура возвращает сведения об указанных (по имени и фамилии) сотрудниках из представления. Для поиска сотрудников хранимая процедура применяет указанный шаблон. Если он не задан, используется предустановленное значение (фамилии, начинающиеся с буквы В).

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

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

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

Г. Использование параметров OUTPUT

В следующем примере создается хранимая процедура uspGetList, возвращающая список товаров, цена которых не превышает заданное значение. На данном примере показывается использование нескольких инструкций SELECT и нескольких параметров OUTPUT. Параметры OUTPUT позволяют внешней процедуре, пакету или нескольким инструкциям Transact-SQL осуществлять доступ к набору значений во время выполнения процедуры.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Выполните процедуру uspGetList для возврата списка продуктов Adventure Works (велосипеды), стоимость которых не превышает 700 рублей. Параметры OUTPUT @Cost и @ComparePrices используются совместно с языком управляющих операторов для возврата сообщения в окне Сообщения.

Примечание.
Переменная OUTPUT должна быть установлена во время создания процедуры, а также при использовании переменной. Имена параметра и переменной не должны совпадать; должны совпадать тип данных и положение параметра (если только не используется выражение @ListPrice= variable).

Переменная OUTPUT должна быть установлена во время создания процедуры, а также при использовании переменной. Имена параметра и переменной не должны совпадать; должны совпадать тип данных и положение параметра (если только не используется выражение @ListPrice= variable).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Ниже приводится частичный результирующий набор.

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

См. также

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

Присвоение имен хранимым процедурам

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

Примеры

А. Использование простой процедуры в сложной инструкции SELECT

Б. Использование простой процедуры с параметрами

В. Использование простой процедуры с подстановочными параметрами

Г. Использование параметров OUTPUT

См. также

Задачи

Основные понятия

Другие ресурсы

Справка и поддержка

Дополнительные ресурсы