Статья
04/01/2012

CREATE PROCEDURE (Transact-SQL)

Создает хранимую процедуру Transact-SQL или хранимую процедуру CLR в SQL Server 2008 R2. Хранимые процедуры в аналогичны процедурам в других языках программирования:

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

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

Значок ссылки на раздел Синтаксические обозначения Transact-SQL

Синтаксис

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Аргументы

schema_name
Имя схемы, которой принадлежит процедура. Процедуры привязаны к схеме. Если имя схемы не указано при создании процедуры, то автоматически назначается схема по умолчанию для пользователя, который создает процедуру. Дополнительные сведения о схемах см. в разделе Отделение пользователей от схем.
procedure_name
Имя процедуры. Имена процедур должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме.

При задании имен для процедур не следует пользоваться префиксом sp_. Этим префиксом в SQL Server обозначаются системные процедуры. Использование этого префикса может нарушить работу кода приложения, если обнаружится системная процедура с таким же именем. Дополнительные сведения см. в разделе Проектирование хранимых процедур (компонент Database Engine).

Локальную или глобальную процедуру можно создать, указав один символ номера (#) перед procedure_name (#procedure_name) в случае локальных временных процедур и два символа номера в случае глобальных временных процедур (##procedure_name). Локальная временная процедура видима только соединению, которое создало процедуру, и удаляется, когда это соединение закрывается. Глобальная временная процедура доступна для всех соединений и удаляется при завершении последнего сеанса, в котором она использовалась. Для процедур CLR нельзя задавать временные имена.

Полное имя процедуры или глобальной временной процедуры не может содержать более 128 символов (с учетом символов ##). Полное имя локальной временной процедуры с учетом символа # не может содержать более 116 символов.

**;**number
Необязательный целочисленный аргумент, используемый для группирования одноименных процедур. Все сгруппированные процедуры можно удалить, выполнив одну инструкцию DROP PROCEDURE.

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

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

**@**parameter
Параметр, объявленный в процедуре. Укажите имя параметра, начинающееся со знака @. Имя параметра должно соответствовать правилам для идентификаторов. Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров.

Можно объявить от 1 до 2100 параметров. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру. Если процедур содержит возвращающие табличное значение параметры, а в вызове отсутствует параметр, то передается пустая таблица. Параметры могут использоваться только вместо постоянных выражений и не могут использоваться вместо имен таблиц, столбцов и других объектов базы данных. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).

Параметры не могут быть объявлены, если указан параметр FOR REPLICATION.
[ type_schema_name**.** ] data_type
Тип данных параметра и схема, к которой принадлежит этот тип.

Рекомендации по типам данных для процедур Transact-SQL:
- Все типы данных Transact-SQL можно использовать в качестве параметров.
- Для создания возвращающих табличное значение параметров можно использовать определяемый пользователем табличный тип. Возвращающие табличное значение параметры могут быть только ВХОДНЫМИ и должны сопровождаться ключевым словом READONLY. Дополнительные сведения см. в разделе Возвращающие табличное значение параметры (компонент Database Engine).
- Типы данных cursor могут быть только ВЫХОДНЫМИ параметрами и должны сопровождаться ключевым словом VARYING.
Рекомендации по типам данных для процедур CLR:
- Все собственные типы данных SQL Server, имеющие эквиваленты в управляемом коде, можно использовать в качестве параметров. Дополнительные сведения о соответствии между типами среды CLR и системными типами данных SQL Server см. в разделе Сопоставление данных о параметрах CLR. Дополнительные сведения о системных типах данных SQL Server и их синтаксисе см. в разделе Типы данных (Transact-SQL).
- Возвращающие табличное значение типы данных и типы данных cursor не могут служить параметрами.
- Если тип параметра является определяемым пользователем типом данных CLR, то необходимо иметь связанное с этим типом разрешение EXECUTE.
VARYING
Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот параметр динамически формируется процедурой, и его содержимое может различаться. Применяется только к аргументам типа cursor. Этот параметр недопустим для процедур CLR.
default
Значение по умолчанию для параметра. Если для некоторого параметра определено значение по умолчанию, то процедуру можно выполнить без указания значения этого параметра. Значение по умолчанию должно быть константой или может быть равно NULL. Значение константы может иметь вид шаблона, что позволяет использовать ключевое слово LIKE при передаче параметра в процедуру. См. пример В далее.

Значения по умолчанию записываются в столбец sys.parameters.default только для процедур CLR. В случае параметров аргументов Transact-SQL этот столбец будет содержать значения NULL.
OUT | OUTPUT
Показывает, что параметр процедуры является выходным. Используйте выходные параметры для возврата значений коду, вызвавшему процедуру. Аргументы типа text, ntext и image не могут быть выходными, если процедура не является процедурой CLR. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR. Возвращающий табличное значение тип данных не может быть указан в качестве выходного параметра процедуры.
READONLY
Указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является возвращающим табличное значение типом, то должно быть указано ключевое слово READONLY.
RECOMPILE
Показывает, что компонент Database Engine не кэширует план запроса для этой процедуры, что вызывает ее компиляцию при каждом выполнении. Дополнительные сведения о причинах принудительной повторной компиляции см. в разделе Перекомпиляция хранимых процедур. Этот параметр нельзя использовать, если указано предложение FOR REPLICATION, а также для процедур CLR.

Чтобы компонент Database Engine удалил планы для отдельных запросов в процедуре, следует использовать подсказку в запросе RECOMPILE в определении запроса. Дополнительные сведения см. в разделе Подсказки в запросах (Transact-SQL).
ENCRYPTION
Показывает, что SQL Server выполнит затемнение исходного текста инструкции CREATE PROCEDURE. Результат запутывания не виден непосредственно ни в одном из представлений каталога в SQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить запутанный текст, однако этот текст будет доступен привилегированным пользователям, которые либо смогут обращаться к системным таблицам через порт DAC, либо будут иметь непосредственный доступ к файлам баз данных. Кроме того, пользователи, имеющие право на подключение отладчика к процессу на сервере, могут получить расшифрованный текст процедуры из памяти во время выполнения. Дополнительные ведения о доступе к метаданным системы см. в разделе Настройка видимости метаданных.

Этот параметр недопустим для процедур CLR.

Процедуры, созданные с этим аргументом, не могут быть опубликованы как часть репликации SQL Server.
Инструкция EXECUTE AS.
Определяет контекст безопасности, в котором должна быть выполнена процедура.

Дополнительные сведения см. в разделе EXECUTE AS, предложение (Transact-SQL).
FOR REPLICATION
Указывает, что процедура создается для репликации. Следовательно, ее нельзя выполнять на подписчике. Процедура, созданная с параметром FOR REPLICATION, используется в качестве фильтра и выполняется только в процессе репликации. Параметры не могут быть объявлены, если указан параметр FOR REPLICATION. Параметр FOR REPLICATION нельзя указывать для процедур CLR. Параметр RECOMPILE не учитывается для процедур, созданных с параметром FOR REPLICATION.

Процедура с параметром FOR REPLICATION будет иметь в представлении sys.objects и sys.procedures тип объекта RF.
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Одна или несколько инструкций Transact-SQL, составляющих текст процедуры. Инструкции можно заключить в необязательные ключевые слова BEGIN и END. Дополнительные сведения см. далее в разделах «Рекомендации», «Общие замечания» и «Ограничения».
EXTERNAL NAME assembly_name**.class_name.method_name
Указывает метод сборки .NET Framework, на который ссылается процедура CLR. Параметр class_name должен быть допустимым идентификатором SQL Server и должен существовать в сборке в виде класса. Если имя класса включает названия пространств имен, отделенные точками (.), оно должно быть ограничено при помощи квадратных скобок ([]) или двойных кавычек (""**). Указанный метод класса должен быть статическим.

По умолчанию SQL Server не производит выполнение кода CLR. Допускается создание, изменение и удаление объектов базы данных, содержащих ссылки на модули среды CLR, однако SQL Server их не выполняет до тех пор, пока не будет включен параметр clr enabled. Для включения этого параметра используйте хранимую процедуру sp_configure.

Общие замечания

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

Переменные в процедуре могут определяться пользователем или быть системными, такими как @@SPID.

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

Процедуры могут выполняться автоматически при запуске SQL Server. Они должны быть созданы системным администратором в базе данных master и выполняться в контексте предопределенной роли сервера sysadmin в фоновом процессе. Они не могут иметь ни входных, ни выходных параметров. Дополнительные сведения см. в разделе Выполнение хранимых процедур (компонент Database Engine).

Процедуры называются вложенными, если одна процедура вызывает другую или выполняет управляемый код по ссылке на подпрограмму, тип или статистическое выражение CLR. Процедуры и ссылки на управляемый код могут быть вложены не более чем на 32 уровня. Уровень вложенности увеличивается на единицу, когда вызванная процедура или управляемый код начинает выполняться, и уменьшается на единицу, когда заканчивает выполнение. Методы, вызываемые из управляемого кода, не учитываются в этом ограничении. Однако, если хранимая процедура CLR обращается к данным через управляемый поставщик SQL Server, для передачи данных из управляемого кода в SQL добавляется дополнительный уровень вложенности.

Если уровень вложенности превышает максимальное значение, вся цепочка вызовов заканчивается ошибкой. Получить уровень вложенности текущей выполняемой хранимой процедуры можно с помощью функции @@NESTLEVEL.

Совместимость

При создании или изменении процедуры Transact-SQL компонент Database Engine сохраняет значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS. Эти первоначальные значения используются при выполнении процедуры. Таким образом, пока процедура выполняется, любые значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS, задаваемые во время клиентского сеанса, не учитываются.

Другие параметры SET, такие как SET ARITHABORT, SET ANSI_WARNINGS или SET ANSI_PADDINGS, при создании или изменении процедуры не сохраняются. Если логика процедуры зависит от конкретного значения параметра, включите в начало процедуры инструкцию SET, чтобы гарантировать нужное значение. Если инструкция SET выполняется из процедуры, то значение действует только до завершения процедуры. После этого оно принимает прежнее значение, которое имело место при вызове процедуры. Это позволяет клиентам задавать нужные им параметры без влияния на логику процедуры.

Внутри процедуры может быть указана любая инструкция SET, за исключением SET SHOWPLAN_TEXT и SET SHOWPLAN_ALL. Эти инструкции могут встречаться только в пакете. Выбранный параметр SET остается в силе до завершения процедуры, после чего восстанавливает прежнее значение. Дополнительные сведения о параметрах SET см. в разделе Параметры SET.

Примечание
Значение SET ANSI_WARNINGS не учитывается при передаче аргументов процедуре или определяемой пользователем функции, а также при объявлении и задании переменных в инструкции пакета. Например, если объявить переменную как char, а затем присвоить ей значение длиннее трех символов, то данные будут усечены до определенного размера, а инструкция INSERT или UPDATE завершится успешно.

Значение SET ANSI_WARNINGS не учитывается при передаче аргументов процедуре или определяемой пользователем функции, а также при объявлении и задании переменных в инструкции пакета. Например, если объявить переменную как char, а затем присвоить ей значение длиннее трех символов, то данные будут усечены до определенного размера, а инструкция INSERT или UPDATE завершится успешно.

Ограничения

Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете.

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

CREATE AGGREGATE	CREATE SCHEMA	SET SHOWPLAN_TEXT
CREATE DEFAULT	CREATE или ALTER TRIGGER	SET SHOWPLAN_XML
CREATE или ALTER FUNCTION	CREATE или ALTER VIEW	USE database_name
CREATE или ALTER PROCEDURE	SET PARSEONLY
CREATE RULE	SET SHOWPLAN_ALL

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

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

-- Passing the function value as a variable.
DECLARE @CheckDate datetime = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; 
GO

Если процедура вносит изменения на удаленном экземпляре SQL Server, откат этих изменений будет невозможен. Удаленные процедуры не участвуют в транзакциях. Дополнительные сведения см. в разделе Обработка ошибок в удаленных хранимых процедурах «сервер-сервер».

Чтобы компонент Database Engine правильно выбрал перегруженную в .NET Framework версию метода, в предложении EXTERNAL NAME необходимо указывать метод следующим образом.

Он должен быть объявлен как статический метод.
Он должен принимать то же количество параметров, что и процедура.
Типы параметров метода должны быть совместимы с типами соответствующих параметров процедуры SQL Server. Сведения о соответствии между типами данных SQL Server и .NET Framework см. в разделе Сопоставление данных о параметрах CLR.

Метаданные

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

Представление	Описание
sys.sql_modules	Возвращает определение процедуры Transact-SQL. Текст процедуры, созданной с параметром ENCRYPTION, нельзя увидеть при помощи представления каталога sys.sql_modules.
sys.assembly_modules	Возвращает сведения о процедуре CLR.
sys.parameters	Возвращает сведения о параметрах, определенных в процедуре
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Возвращает объекты, на которые ссылается процедура.

Чтобы оценить размер процедуры после компиляции, пользуйтесь счетчиками системного монитора «Коэффициент попадания в кэш», «Страницы кэша» и «Счетчик объектов в кэше». Дополнительные сведения см. в разделе SQL Server, объект Plan Cache.

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

Разрешения

Требуется разрешение CREATE PROCEDURE для базы данных и разрешение ALTER для схемы, в которой создается процедура, либо членство в предопределенной роли базы данных db_ddladmin.

Для хранимых процедур CLR пользователь должен быть владельцем сборки, на которую ссылается предложение EXTERNAL NAME, либо иметь разрешение REFERENCES для этой сборки.

Примеры

Категория	Используемые элементы синтаксиса
Базовый синтаксис	CREATE PROCEDURE
Передача параметров	@parameter • = значение по умолчанию • OUTPUT • тип возвращающего табличное значение параметра • CURSOR VARYING
Изменение данных с помощью хранимой процедуры	UPDATE
Обработка ошибок	TRY…CATCH
Скрытие определения процедуры	WITH ENCRYPTION
Принудительная перекомпиляция хранимой процедуры	WITH RECOMPILE
Задание контекста безопасности	EXECUTE AS

Базовый синтаксис

В примерах из этого раздела показаны основные возможности инструкции CREATE PROCEDURE и используется минимально необходимый синтаксис.

А. Создание простой процедуры Transact-SQL

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

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
CREATE PROCEDURE dbo.uspMultipleResults 
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

В. Создание хранимой процедуры CLR

В следующем примере создается процедура GetPhotoFromDB, ссылающаяся на метод GetPhotoFromDB класса LargeObjectBinary из сборки HandlingLOBUsingCLR . Перед созданием процедуры сборка HandlingLOBUsingCLR регистрируется в локальной базе данных.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

Передача параметров

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

А. Создание простой процедуры со входными параметрами

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

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';

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

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

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%';

В. Использование выходных параметров

В следующем примере создается процедура 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. Выходные (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.

Г. Использование возвращающих табличные значения параметров

В следующем примере показано использование возвращающего табличное значение параметра для вставки в таблицу нескольких строк. В примере создается тип параметра, объявляется табличная переменная для ссылки, заполняется список параметров, а затем значения передаются в хранимую процедуру. Хранимая процедура использует эти значения для вставки в таблицу нескольких строк.

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

Д. Использование выходного параметра-курсора

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

Сначала следует создать процедуру, объявляющую и открывающую курсор для таблицы Currency:

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

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

USE AdventureWorks2008R2;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Изменение данных с помощью хранимой процедуры

Примеры в этом разделе демонстрируют вставку или изменение данных в таблицах или представлениях путем включения инструкции DML в определение процедуры.

A. Использование UPDATE в хранимой процедуре

В следующем примере инструкция UPDATE используется в хранимой процедуре. Процедура принимает один входной параметр @NewHours и один выходной параметр @RowCount. Значение параметра @NewHours используется в инструкции UPDATE для обновления столбца VacationHours в таблице HumanResources.Employee. Выходной параметр @RowCount используется для возврата значения числа задействованных строк в локальную переменную. Выражение CASE используется в предложении SET для условного определения значения, задаваемого для столбца VacationHours. Если для сотрудника определена почасовая оплата (SalariedFlag = 0), столбцу VacationHours присваивается текущее количество часов плюс значение, указанное в параметре @NewHours; в противном случае столбцу VacationHours присваивается значение, указанное в параметре @NewHours.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

Обработка ошибок

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

Использование конструкции TRY…CATCH

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

USE AdventureWorks2008R2;
GO

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS
SET NOCOUNT ON;
BEGIN TRY
   BEGIN TRANSACTION 
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;

/* Intentionally generate an error by reversing the order in which rows are deleted from the
   parent and child tables. This change does not cause an error when the procedure
   definition is altered, but produces an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS

BEGIN TRY
   BEGIN TRANSACTION 
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT TRANSACTION

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;

DROP PROCEDURE Production.uspDeleteWorkOrder;

Скрытие определения процедуры

В примерах этого раздела показано, как скрыть определение хранимой процедуры.

А. Использование параметра WITH ENCRYPTION

В следующем примере создается процедура HumanResources.uspEncryptThis.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

Параметр WITH ENCRYPTION скрывает определение процедуры при запросе системного каталога или использовании функций метаданных, как показано в следующих примерах.

Выполните процедуру sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Напрямую выполите запрос к представлению каталога sys.sql_modules:

USE AdventureWorks2008R2;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

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

definition

--------------------------------

NULL

Принудительная перекомпиляция хранимой процедуры

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

А. Использование параметра WITH RECOMPILE

Предложение WITH RECOMPILE может оказаться полезным в том случае, если передаваемые в процедуру параметры будут нетипичными или если новый план выполнения процедуры не следует кэшировать или хранить в памяти.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

Задание контекста безопасности

В примерах этого раздела предложение EXECUTE AS служит для задания контекста безопасности, в котором выполняется хранимая процедура.

А. Использование предложения EXECUTE AS

Следующий пример показывает использование предложения EXECUTE AS для указания контекста безопасности, в котором может выполняться процедура. В данном случае параметр CALLER свидетельствует о том, что процедура может быть выполнена в контексте вызывающего ее пользователя.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

Б. Создание пользовательских наборов разрешений

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

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

См. также

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