Статья
04/01/2012

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

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

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

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

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

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

Функции обработки ошибок, такие как ERROR_LINE и ERROR_PROCEDURE, можно использовать в хранимых процедурах. Дополнительные сведения см. в разделе Использование конструкции TRY...CATCH в Transact-SQL.

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

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

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

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
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 AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
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 AdventureWorks2008R2;
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,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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 AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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 AdventureWorks2008R2;
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. Параметры @Cost и @ComparePrices типа OUTPUT используются в языке управления выполнением для вывода информации в окне Сообщения.

Примечание
Переменная 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

См. также

Задания

Справочник

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

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

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