CREATE PROCEDURE (Transact-SQL)

Изменения: 12 декабря 2006 г.

Создает хранимую процедуру. Хранимая процедура — это сохраненная совокупность инструкций языка Transact-SQL или ссылка на метод среды CLR платформы Microsoft .NET Framework, которая может принимать и возвращать предоставленные пользователем параметры. Процедуры можно создавать для постоянного использования, для временного использования в одном сеансе (локальная временная процедура) или для временного использования во всех сеансах (глобальная временная процедура).

Хранимые процедуры могут выполняться автоматически при запуске экземпляра SQL Server.

Синтаксические обозначения в Transact-SQL

Синтаксис

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
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. Например, в приложении orders можно было бы использовать процедуры с именами orderproc;1, orderproc;2 и т. д. Инструкция DROP PROCEDURE orderproc удалила бы все процедуры из этой группы. Если имя содержит идентификаторы с разделителями, номер не должен быть частью идентификатора; следует выделять при помощи подходящего разделителя только procedure_name.

  На пронумерованные хранимые процедуры распространяются следующие ограничения:

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

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

• **@**parameter
  Параметр процедуры. В инструкции CREATE PROCEDURE можно объявить один или более параметров. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру. Хранимая процедура может иметь не более 2 100 параметров.

  Следует указывать имя параметра, используя в качестве первого символа знак @. Имя параметра должно соответствовать правилам для идентификаторов. Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров. По умолчанию параметры могут использоваться только в качестве константных выражений; они не могут быть использованы вместо имен таблиц, столбцов или других объектов базы данных. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).

  Параметры не могут быть объявлены, если указан параметр FOR REPLICATION.

• [ type_schema_name**.** ] data_type
  Тип данных параметра и схема, к которой он относится. Параметрами хранимых процедур Transact-SQL могут быть любые типы данных, за исключением table. Тип данных cursor может быть использован только в качестве выходного (OUTPUT) параметра. При указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько.

  Параметры хранимых процедур среды CLR не могут иметь тип char, varchar, text, ntext, image, cursor и table. Дополнительные сведения о соответствии между типами среды CLR и системными типами данных SQL Server см. в разделе SQL Server Data Types and Their .NET Framework Equivalents. Дополнительные сведения о системных типах данных SQL Server и их синтаксисе см. в разделе Типы данных (Transact-SQL).

  Если тип параметра является пользовательским типом данных CLR, то необходимо иметь связанное с этим типом разрешение EXECUTE.

  Если аргумент type_schema_name не указан, компонент SQL Server 2005 Database Engine ссылается на type_name в следующем порядке:

  • Системные типы данных SQL Server.
  • Схема по умолчанию текущего пользователя в текущей базе данных.
  • Схема dbo текущей базы данных.

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

• VARYING
  Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот аргумент динамически формируется хранимой процедурой, и его содержимое может различаться. Применяется только к аргументам типа cursor.

• default
  Значение по умолчанию для аргумента. Если значение default определено, процедуру можно выполнить без указания значения соответствующего аргумента. Значение по умолчанию должно быть константой или может равняться NULL. Если в процедуре используется аргумент с ключевым словом LIKE, он может включать символы-шаблоны %, _, [] и [^].

  Примечание.
  Значения по умолчанию записываются в столбец sys.parameters.default только для процедур среды CLR. В случае параметров аргументов Transact-SQL этот столбец будет содержать значения NULL.

• OUTPUT
  Показывает, что аргумент процедуры является выходным. Значение этого аргумента можно получить при помощи инструкции EXECUTE. Используйте выходные аргументы для возврата значений коду, вызвавшему процедуру. Аргументы типов text, ntext и image не могут быть выходными, если процедура не является процедурой CLR. Выходным аргументом с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR.

• RECOMPILE
  Показывает, что компонент Database Engine не кэширует план выполнения процедуры и что процедура компилируется во время выполнения. Этот аргумент нельзя использовать, если указан аргумент FOR REPLICATION. Задать аргумент RECOMPILE для хранимой процедуры CLR нельзя.

  Чтобы компонент Database Engine удалил планы выполнения отдельных запросов в хранимой процедуре, следует использовать подсказку в запросе RECOMPILE. Дополнительные сведения см. в разделе Подсказка в запросе (Transact-SQL). Подсказку в запросе RECOMPILE следует использовать в тех случаях, когда необычные или временные значения используются только в части запросов, входящих в состав хранимой процедуры.

• ENCRYPTION
  Показывает, что SQL Server выполнит затемнение исходного текста инструкции CREATE PROCEDURE. Результат затемнения не виден непосредственно ни в одном представлении каталога SQL Server 2005. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить затемненный текст. Однако этот текст будет доступен привилегированным пользователям, которые смогут обращаться к системным таблицам либо через порт 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.

• <sql_statement>
  Одна или несколько инструкций языка Transact-SQL, которые будут включены в состав процедуры. При этом действуют некоторые ограничения, описанные в разделе «Примечания».

• 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.

Замечания

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

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

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

По умолчанию параметры могут принимать значения NULL. Если параметр, имеющий значение NULL, используется в инструкции CREATE TABLE или ALTER TABLE при обращении к столбцу, не поддерживающему значения NULL, то компонент Database Engine возвращает ошибку. Чтобы предотвратить передачу значений NULL столбцу, который их не поддерживает, следует реализовать в процедуре соответствующую логику или передать столбцу значение по умолчанию при помощи ключевого слова DEFAULT инструкции CREATE TABLE или ALTER TABLE.

Для каждого столбца во временной таблице рекомендуется явно указывать атрибут NULL или NOT NULL. Если атрибуты NULL или NOT NULL не указаны в инструкции CREATE TABLE или ALTER TABLE, то способ назначения этих атрибутов столбцам компонентом Database Engine определяется параметрами ANSI_DFLT_ON и ANSI_DFLT_OFF. Если в контексте соединения выполняется хранимая процедура с настройками этих параметров, отличными от настроек соединения, в котором была создана процедура, столбцы таблицы, созданной для второго соединения, могут отличаться по признаку поддержки значений NULL и работать иначе. Если атрибут NULL или NOT NULL явно задан для каждого столбца, временные таблицы создаются с одним и тем же признаком поддержки значений NULL во всех соединениях, в которых выполняется хранимая процедура.

Использование параметров SET

При создании или изменении хранимой процедуры 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 выполняется из хранимой процедуры, устанавливаемое ею значение действует только до завершения хранимой процедуры. После этого оно принимает прежнее значение, которое имело место при вызове хранимой процедуры. Это позволяет клиентам задавать нужные им значения без влияния на логику хранимой процедуры.

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

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

Параметры хранимой процедуры среды CLR могут иметь любой скалярный системный тип данных SQL Server.

Чтобы компонент Database Engine выбрал корректный вариант перегруженного метода .NET Framework, метод, указанный при помощи <квалификатора_метода>, должен иметь следующие характеристики:

• Он должен быть объявлен как статический метод.
• Он должен принимать то же количество параметров, что и процедура.
• Метод не должен быть конструктором или деструктором класса.
• Типы параметров метода должны быть совместимы с типами соответствующих параметров процедуры SQL Server. Сведения о соответствии между типами данных SQL Server и .NET Framework см. в разделе SQL Server Data Types and Their .NET Framework Equivalents.
• Метод должен возвращать либо void, либо значение типа SQLInt32, SQLInt16, System.Int32 или System.Int16.
• Если какой-либо параметр объявлен как выходной (OUTPUT), метод должен возвращать параметры по ссылке, а не по значению.

Получение информации о хранимых процедурах

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

Например:

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

Примечание.
Текст хранимой процедуры, созданной с параметром ENCRYPTION, нельзя увидеть при помощи представления каталога sys.sql_modules.

Для получения информации об объектах, на которые ссылается процедура, следует запросить представление каталога sys.sql_dependencies или воспользоваться хранимой процедурой sp_depends. Хранимая процедура sp_depends не возвращает информацию об объектах, на которые ссылаются хранимые процедуры CLR. Для получения информации о хранимых процедурах CLR следует использовать представление каталога sys.assembly_modules в базе данных, к которой относится процедура.

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

Отложенное разрешение имен

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

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

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

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

Дополнительные сведения см. в разделе Выполнение хранимых процедур (компонент Database Engine).

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

Параметры типа cursor

В хранимых процедурах Transact-SQL только выходные (OUTPUT) параметры могут иметь тип cursor. Если параметр имеет тип cursor, он должен быть объявлен как VARYING и OUTPUT. Если параметр объявлен с ключевым словом VARYING, он должен иметь тип cursor и должно быть указано ключевое слово OUTPUT. Дополнительные сведения см. в разделе Использование типа данных cursor в параметре OUTPUT.

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

Компонент Database Engine поддерживает два типа временных процедур: локальные и глобальные. Локальная временная процедура видима только в контексте того соединения, в котором она была создана. Глобальная временная процедура доступна в контексте любого соединения. При завершении текущего сеанса локальные временные процедуры автоматически сбрасываются. Глобальная временная процедура сбрасывается при завершении последнего сеанса, в котором она использовалась. Дополнительные сведения см. в разделе Создание хранимых процедур (компонент Database Engine).

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

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

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

Хранимые процедуры могут быть вложенными. Иными словами, одна хранимая процедура может вызывать другую. Уровень вложенности увеличивается на 1, когда начинается выполнение вызванной процедуры, и уменьшается на 1, когда вызванная процедура завершается. Уровень вложенности хранимых процедур может достигать 32. Дополнительные сведения см. в разделе Вложенные хранимые процедуры.

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

Имя объекта системного монитора	Имя счетчика системного монитора
SQLServer: кэш планов	Коэффициент попадания в кэше
	Страницы кэша
	Счетчик объектов в кэше*

* Эти счетчики доступны для разных категорий объектов кэша, включая нерегламентированные SQL-запросы, подготовленные SQL-запросы, процедуры, триггеры и т. д.

Дополнительные сведения см. в разделе SQL Server, объект Plan Cache.

Ограничения параметра <sql_statement>

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

Если хранимую процедуру будут выполнять пользователи, не являющиеся ее владельцами, имена объектов, используемых внутри процедуры в любых инструкциях DDL, таких как CREATE, ALTER или DROP, инструкциях DBCC, EXECUTE и динамические инструкциях SQL должны быть дополнены именем схемы объекта. Дополнительные сведения см. в разделе Проектирование хранимых процедур (компонент Database Engine).

Разрешения

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

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

Примеры

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

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

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

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

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

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.

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.

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

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

USE AdventureWorks;
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.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

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

Следующий пример создает хранимую процедуру HumanResources.uspEncryptThis.

USE AdventureWorks;
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 EmployeeID, Title, 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 AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

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

definition
----------------------
NULL

(1 row(s) affected)

Ж. Использование отложенного разрешения имен

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

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

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

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

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

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

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

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

USE AdventureWorks;
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 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
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

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

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

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

USE AdventureWorks;
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 AdventureWorks;
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

См. также

Справочник

Инструкция ALTER PROCEDURE (Transact-SQL)
Язык управления потоком (Transact-SQL)
Типы данных (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL)
EVENTDATA (Transact-SQL)
Функции (Transact-SQL)
sp_depends (Transact-SQL)
sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL)
sys.sql_dependencies (Transact-SQL)
sys.assembly_references (Transact-SQL)
sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)

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

Пакеты
Курсоры (ядро СУБД)
Хранимые процедуры (компонент Database Engine)
Использование переменных и параметров (ядро СУБД)
Как создать хранимую процедуру (среда SQL Server Management Studio)

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

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Измененное содержимое В разделе «Примечания» объясняется, что стандартный максимальный размер хранимой процедуры не установлен.