CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Создает Transact-SQLTransact-SQL или хранимую процедуру CLR в SQL ServerSQL Server, База данных SQL AzureAzure SQL Database, хранилище данных SQL Azure и Parallel Data Warehouse.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, База данных SQL AzureAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Хранимые процедуры похожи на процедуры из других языков программирования в том, что они могут:Stored procedures are similar to procedures in other programming languages in that they can:

  • принимать входные параметры и возвращать вызывающей процедуре или пакету ряд значений в виде выходных параметров;Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • содержать программные инструкции, которые выполняют операции в базе данных, в том числе вызывающие другие процедуры;Contain programming statements that perform operations in the database, including calling other procedures.

  • возвращать значение состояния вызывающей процедуре или пакету, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

Используйте эту инструкцию для создания постоянной процедуры в текущей базе данных или временной процедуры в базе данных tempdb.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

Примечание

В этом разделе рассматривается интеграция среды CLR .NET Framework с SQL Server.The integration of .NET Framework CLR into SQL Server is discussed in this topic. Интеграция со средой CLR не применяется к База данных SQLSQL Database Azure.CLR integration does not apply to Azure База данных SQLSQL Database.

Перейдите к разделу Простые примеры, чтобы пропустить сведения о синтаксисе и просмотреть краткий пример базовой хранимой процедуры.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] { 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 ]  
-- Transact-SQL Syntax for CLR Stored Procedures  
  
CREATE [ OR ALTER ] { 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 }  
[;]  
-- Transact-SQL Syntax for Natively Compiled Stored Procedures  
  
CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] 
        [ OUT | OUTPUT ] [READONLY] 
    ] [ ,... n ]  
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]  
AS  
{  
  BEGIN ATOMIC WITH (set_option [ ,... n ] )  
sql_statement [;] [ ... n ]  
 [ END ]  
}  
 [;]  
  
<set_option> ::=  
    LANGUAGE =  [ N ] 'language'  
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }  
  | [ DATEFIRST = number ]  
  | [ DATEFORMAT = format ]  
  | [ DELAYED_DURABILITY = { OFF | ON } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  
  
-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

АргументыArguments

OR ALTEROR ALTER
Область применения: Azure База данных SQLSQL Database, SQL ServerSQL Server (начиная с SQL Server 2016 (13.x)SQL Server 2016 (13.x) с пакетом обновления 1 (SP1)).Applies to: Azure База данных SQLSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

Изменяет процедуру, если она уже существует.Alters the procedure if it already exists.

schema_nameschema_name
Имя схемы, которой принадлежит процедура.The name of the schema to which the procedure belongs. Процедуры привязаны к схеме.Procedures are schema-bound. Если имя схемы не указано при создании процедуры, то автоматически назначается схема по умолчанию для пользователя, который создает процедуру.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
Имя процедуры.The name of the procedure. Имена процедур должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме.Procedure names must comply with the rules for identifiers and must be unique within the schema.

При задании имен для процедур не следует пользоваться префиксом sp_ .Avoid the use of the sp_ prefix when naming procedures. Этим префиксом в SQL ServerSQL Server обозначаются системные процедуры.This prefix is used by SQL ServerSQL Server to designate system procedures. Использование этого префикса может нарушить работу кода приложения, если обнаружится системная процедура с таким же именем.Using the prefix can cause application code to break if there is a system procedure with the same name.

Локальную или глобальную временную процедуру можно создать, указав один символ номера (#) перед procedure_name ( #procedure_name) для локальных временных процедур и два символа номера для глобальных временных процедур ( ##procedure_name).Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Локальная временная процедура видима только соединению, которое создало процедуру, и удаляется, когда это соединение закрывается.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. Глобальная временная процедура доступна для всех соединений и удаляется при завершении последнего сеанса, в котором она использовалась.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Для процедур CLR нельзя задавать временные имена.Temporary names cannot be specified for CLR procedures.

Полное имя процедуры или глобальной временной процедуры не может содержать более 128 символов (с учетом символов ##).The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. Полное имя локальной временной процедуры с учетом символа # не может содержать более 116 символов.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; number; number
Применимо к: SQL Server 2008SQL Server 2008 и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2008SQL Server 2008 and later and База данных SQL AzureAzure SQL Database.

Необязательный целочисленный аргумент, используемый для группирования одноименных процедур.An optional integer that is used to group procedures of the same name. Все сгруппированные процедуры можно удалить, выполнив одну инструкцию DROP PROCEDURE.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Примечание

Этот компонент находится в режиме обслуживания и может быть удален в будущей версии Microsoft SQL Server.This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

В нумерованных процедурах нельзя использовать определяемые пользователем типы данных xml и CLR, и их нельзя использовать в структуре плана.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@ parameter@ parameter
Параметр, объявленный в процедуре.A parameter declared in the procedure. Укажите имя параметра, начинающееся со знака @ .Specify a parameter name by using the at sign (@) as the first character. Имя параметра должно соответствовать правилам для идентификаторов.The parameter name must comply with the rules for identifiers. Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров.Parameters are local to the procedure; the same parameter names can be used in other procedures.

Можно объявить от 1 до 2100 параметров.One or more parameters can be declared; the maximum is 2,100. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. Если процедура содержит возвращающие табличное значение параметры, а в вызове отсутствует параметр, передается пустая таблица.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. Параметры могут использоваться только в качестве выражений-констант; они не могут использоваться вместо имен таблиц, столбцов или других объектов базы данных.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

Параметры не могут быть объявлены, если указан параметр FOR REPLICATION.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name .[ type_schema_name. ] data_type] data_type
Тип данных параметра и схема, к которой принадлежит этот тип.The data type of the parameter and the schema to which the data type belongs.

Рекомендации по процедурам Transact-SQLTransact-SQLGuidelines for Transact-SQLTransact-SQL procedures:

  • Все типы данных Transact-SQLTransact-SQL можно использовать в качестве параметров.All Transact-SQLTransact-SQL data types can be used as parameters.

  • Для создания возвращающих табличное значение параметров можно использовать определяемый пользователем табличный тип.You can use the user-defined table type to create table-valued parameters. Возвращающие табличное значение параметры могут быть только ВХОДНЫМИ и должны сопровождаться ключевым словом READONLY.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. Дополнительные сведения см. в статье Использование возвращающих табличные значения параметров (ядро СУБД).For more information, see Use Table-Valued Parameters (Database Engine)

  • Типы данных cursor могут быть только ВЫХОДНЫМИ параметрами и должны сопровождаться ключевым словом VARYING.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

Рекомендации по процедурам CLRGuidelines for CLR procedures:

  • Все собственные типы данных SQL ServerSQL Server, имеющие эквиваленты в управляемом коде, можно использовать в качестве параметров.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. Дополнительные сведения о соответствии между типами среды CLR и системными типами данных SQL ServerSQL Server см. в разделе Сопоставление данных параметров CLR.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. Дополнительные сведения о системных типах данных SQL ServerSQL Server и их синтаксисе см. в разделе Типы данных (Transact-SQL).For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • Возвращающие табличное значение типы данных и типы данных cursor не могут служить параметрами.Table-valued or cursor data types cannot be used as parameters.

  • Если тип параметра является определяемым пользователем типом данных CLR, то необходимо иметь связанное с этим типом разрешение EXECUTE.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
Указывает результирующий набор, поддерживаемый в качестве выходного параметра.Specifies the result set supported as an output parameter. Этот параметр динамически формируется процедурой, и его содержимое может различаться.This parameter is dynamically constructed by the procedure and its contents may vary. Область применения — только параметры cursor.Applies only to cursor parameters. Этот параметр недопустим для процедур CLR.This option is not valid for CLR procedures.

defaultdefault
Значение по умолчанию для параметра.A default value for a parameter. Если для некоторого параметра определено значение по умолчанию, то процедуру можно выполнить без указания значения этого параметра.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. Значение по умолчанию должно быть константой или может быть равно NULL.The default value must be a constant or it can be NULL. Значение константы может иметь вид шаблона, что позволяет использовать ключевое слово LIKE при передаче параметра в процедуру.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

Значения по умолчанию записываются в столбец sys.parameters.default только для процедур CLR.Default values are recorded in the sys.parameters.default column only for CLR procedures. В случае параметров процедуры Transact-SQLTransact-SQL этот столбец содержит значения NULL.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
Показывает, что параметр процедуры является выходным.Indicates that the parameter is an output parameter. Используйте выходные параметры для возврата значений коду, вызвавшему процедуру.Use OUTPUT parameters to return values to the caller of the procedure. Параметры text, ntext и image не могут быть выходными, если процедура не является процедурой CLR.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR.An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. Возвращающий табличное значение тип данных не может быть указан в качестве выходного параметра процедуры.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
Указывает, что параметр не может быть обновлен или изменен в тексте процедуры.Indicates that the parameter cannot be updated or modified within the body of the procedure. Если тип параметра является возвращающим табличное значение типом, то должно быть указано ключевое слово READONLY.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
Показывает, что компонент Компонент Database EngineDatabase Engine не кэширует план запроса для этой процедуры, что вызывает ее компиляцию при каждом выполнении.Indicates that the Компонент Database EngineDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. Дополнительные сведения о причинах принудительной повторной компиляции см. в разделе Перекомпиляция хранимой процедуры.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. Этот параметр нельзя использовать, если указано предложение FOR REPLICATION, а также для процедур CLR.This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

Чтобы Компонент Database EngineDatabase Engine удалил планы отдельных запросов в процедуре, следует использовать указание запроса RECOMPILE в определении запроса.To instruct the Компонент Database EngineDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
Область применения: SQL Server (SQL Server 2008SQL Server 2008 и выше), База данных SQL AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 and later), База данных SQL AzureAzure SQL Database.

Показывает, что SQL ServerSQL Server выполняет запутывание исходного текста инструкции CREATE PROCEDURE.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. Результат запутывания не виден непосредственно ни в одном представлении каталога SQL ServerSQL Server.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить запутанный текст,Users who have no access to system tables or database files cannot retrieve the obfuscated text. однако этот текст доступен привилегированным пользователям, которые либо смогут обращаться к системным таблицам через порт DAC, либо будут иметь непосредственный доступ к файлам баз данных.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить расшифрованный текст процедуры из памяти во время выполнения.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Дополнительные сведения о доступе к метаданным системы см. в статье Настройка видимости метаданных.For more information about accessing system metadata, see Metadata Visibility Configuration.

Этот параметр недопустим для процедур CLR.This option is not valid for CLR procedures.

Процедуры, созданные с этим аргументом, не могут быть опубликованы как часть репликации SQL ServerSQL Server.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS предложениеEXECUTE AS clause
Определяет контекст безопасности, в котором должна быть выполнена процедура.Specifies the security context under which to execute the procedure.

Для скомпилированных в собственном коде хранимых процедур, начиная с SQL Server 2016 (13.x)SQL Server 2016 (13.x) и в База данных SQL AzureAzure SQL Database, отсутствуют ограничения на предложение EXECUTE AS.For natively compiled stored procedures, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in База данных SQL AzureAzure SQL Database, there are no limitations on the EXECUTE AS clause. В SQL Server 2014 (12.x)SQL Server 2014 (12.x) предложения SELF, OWNER и user_name поддерживаются с помощью хранимых процедур, скомпилированных в собственном коде.In SQL Server 2014 (12.x)SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.

Дополнительные сведения см. в разделе Предложение EXECUTE AS (Transact-SQL).For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
Область применения: SQL Server (SQL Server 2008SQL Server 2008 и выше), База данных SQL AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 and later), База данных SQL AzureAzure SQL Database.

Указывает, что процедура создается для репликации.Specifies that the procedure is created for replication. Следовательно, ее нельзя выполнять на подписчике.Consequently, it cannot be executed on the Subscriber. Процедура, созданная с параметром FOR REPLICATION, используется в качестве фильтра и выполняется только в процессе репликации.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Параметры не могут быть объявлены, если указан параметр FOR REPLICATION.Parameters cannot be declared if FOR REPLICATION is specified. Параметр FOR REPLICATION нельзя указывать для процедур CLR.FOR REPLICATION cannot be specified for CLR procedures. Параметр RECOMPILE не учитывается для процедур, созданных с параметром FOR REPLICATION.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

Процедура FOR REPLICATION имеет тип объекта RF в представлениях sys.objects и sys.procedures.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Одна или несколько инструкций Transact-SQLTransact-SQL, составляющих текст процедуры.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. Инструкции можно заключить в необязательные ключевые слова BEGIN и END.You can use the optional BEGIN and END keywords to enclose the statements. Дополнительные сведения см. далее в разделах "Рекомендации", "Общие замечания" и "Ограничения".For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

EXTERNAL NAME assembly_name . class_name . method_nameEXTERNAL NAME assembly_name.class_name.method_name
Применимо к: SQL Server 2008SQL Server 2008 и выше, База данных SQLSQL Database.Applies to: SQL Server 2008SQL Server 2008 and later, База данных SQLSQL Database.

Указывает метод сборки .NET Framework.NET Framework для процедуры CLR, на которую создается ссылка.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. Аргумент class_name должен быть допустимым идентификатором SQL ServerSQL Server и существовать как класс в сборке.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. Если класс имеет квалифицированное имя пространства имен, которое использует точку ( . ) для разделения частей пространства имен, имя класса разделено скобками ( [] ) или кавычками ( "" ).If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). Указанный метод класса должен быть статическим.The specified method must be a static method of the class.

По умолчанию SQL ServerSQL Server не производит выполнение кода CLR.By default, SQL ServerSQL Server cannot execute CLR code. Можно создавать, изменять и удалять объекты базы данных со ссылками на модули среды CLR, но SQL ServerSQL Server не выполняет их до тех пор, пока не будет включен параметр clr enabled.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. Для включения параметра используйте хранимую процедуру sp_configure.To enable the option, use sp_configure.

Примечание

Процедуры CLR не поддерживаются в автономной базе данных.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Указывает атомарное выполнение хранимой процедуры.Indicates atomic stored procedure execution. Изменения принимаются, либо все изменения откатываются с исключением.Changes are either committed or all of the changes rolled back by throwing an exception. Блок ATOMIC WITH требуется для скомпилированных в собственном коде хранимых процедур.The ATOMIC WITH block is required for natively compiled stored procedures.

Если процедура возвращает результат (явно с помощью инструкции RETURN либо неявно путем завершения выполнения), выполненная работа фиксируется процедурой.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. Если процедура выдает ошибку, выполняется откат работы, выполненной процедурой.If the procedure THROWs, the work performed by the procedure is rolled back.

Параметр XACT_ABORT по умолчанию имеет значение ON внутри блока ATOMIC и не может быть изменен.XACT_ABORT is ON by default inside an atomic block and cannot be changed. Параметр XACT_ABORT указывает, выполняет ли SQL ServerSQL Server автоматический откат текущей транзакции, если инструкция языка Transact-SQLTransact-SQL вызывает ошибку выполнения.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

Следующие параметры SET всегда имеют значение ON внутри блока ATOMIC; параметры нельзя изменить.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

Параметры SET нельзя изменить внутри блоков ATOMIC.SET options cannot be changed inside ATOMIC blocks. Параметры SET пользовательского сеанса не используются в области скомпилированных в собственном коде хранимых процедур.The SET options in the user session are not used in the scope of natively compiled stored procedures. Эти параметры фиксируются во время компиляции.These options are fixed at compile time.

Операции BEGIN, ROLLBACK и COMMIT нельзя использовать внутри блока ATOMIC.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

Для каждой скомпилированной в собственном коде хранимой процедуры существует один блок ATOMIC. Блок входит во внешнюю область процедуры.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. Блоки не могут быть вложенными.The blocks cannot be nested. Дополнительные сведения о скомпилированных в собственном коде хранимых процедурах см. в разделе Хранимые процедуры, скомпилированные в собственном коде.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
Определяет, допустимы ли для параметра значения NULL.Determines whether null values are allowed in a parameter. Значение по умолчанию — NULL.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Указывает, что процедура компилируется в собственном режиме.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING и EXECUTE AS можно указывать в любом порядке.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. Дополнительные сведения см. в статье Хранимые процедуры, скомпилированные в собственном коде.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Гарантирует, что таблицы, на которые ссылается процедура, нельзя удалить или изменить.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING требуется для хранимых процедур, скомпилированных в собственном коде.SCHEMABINDING is required in natively compiled stored procedures. Дополнительные сведения см. в статье Хранимые процедуры, скомпилированные в собственном коде. Ограничения SCHEMABINDING такие же, как и для определяемых пользователем функций.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. Дополнительные сведения см. в подразделе SCHEMABINDING раздела CREATE FUNCTION (Transact-SQL).For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'LANGUAGE = [N] 'language'
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Эквивалентно параметру сеанса SET LANGUAGE (Transact-SQL).Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'language' (обязательный параметр).LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Требуется для хранимых процедур, скомпилированных в собственном коде.Required for natively compiled stored procedures. Указывает уровень изоляции транзакции для хранимой процедуры.Specifies the transaction isolation level for the stored procedure. Существуют следующие параметры выбора.The options are as follows:

Дополнительные сведения об этих параметрах см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
Указывает, что инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. Если другая транзакция изменяет данные, считанные текущей транзакцией, текущая транзакция завершится с ошибкой.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
Указывает следующее.Specifies the following:

  • Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.Statements cannot read data that has been modified but not yet committed by other transactions.
  • Если другая транзакция изменяет данные, считанные текущей транзакцией, текущая транзакция завершается ошибкой.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • Если другая транзакция вставляет новые строки со значениями ключа, которые входят в диапазон ключей, считываемых любой инструкцией текущей транзакции, текущая транзакция завершается ошибкой.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
Указывает на то, что данные, считанные любой инструкцией транзакции, согласованы на уровне транзакции с версией данных, существовавших в ее начале.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numberDATEFIRST = number
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Указывает первый день недели в виде числа от 1 до 7.Specifies the first day of the week to a number from 1 through 7. DATEFIRST — необязательный параметр.DATEFIRST is optional. Если он не указан, то значение выводится из указанного языка.If it is not specified, the setting is inferred from the specified language.

Дополнительные сведения см. в разделе SET DATEFIRST (Transact-SQL).For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatDATEFORMAT = format
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Указывает порядок частей даты (месяца, дня и года) для интерпретации символьных строк date, smalldatetime, datetime, datetime2 и datetimeoffset.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT — необязательный параметр.DATEFORMAT is optional. Если он не указан, то значение выводится из указанного языка.If it is not specified, the setting is inferred from the specified language.

Дополнительные сведения см. в разделе SET DATEFORMAT (Transact-SQL).For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
Применимо к: SQL Server 2014 (12.x)SQL Server 2014 (12.x) и выше, а также База данных SQL AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and База данных SQL AzureAzure SQL Database.

Фиксации транзакции SQL ServerSQL Server могут быть полностью устойчивыми, использовать настройки по умолчанию или быть отложенными устойчивыми.SQL ServerSQL Server transaction commits can be either fully durable, the default, or delayed durable.

Дополнительные сведения см. в разделе Управление устойчивостью транзакций.For more information, see Control Transaction Durability.

Простые примерыSimple Examples

Чтобы помочь вам приступить к работе, ниже приведены два простых примера.To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB; возвращает имя текущей базы данных.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
Можно создать оболочку этой инструкции в хранимой процедуре, например:You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

Вызов хранимой процедуры с помощью инструкции: EXEC What_DB_is_this;Call the store procedure with statement: EXEC What_DB_is_this;

Немного более сложным вариантом является предоставление входного параметра для повышения уровня гибкости процедуры.Slightly more complex, is to provide an input parameter to make the procedure more flexible. Например:For example:

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

Укажите идентификационный номер базы данных при вызове процедуры.Provide a database id number when you call the procedure. Например, EXEC What_DB_is_that 2; возвращает tempdb.For example, EXEC What_DB_is_that 2; returns tempdb.

Дополнительные примеры см.в подразделе Примеры в конце этого раздела.See Examples towards the end of this topic for many more examples.

РекомендацииBest Practices

Это неполный список рекомендаций, однако данные советы помогут повысить производительность процедур.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Начинайте текст процедуры с инструкции SET NOCOUNT ONUse the SET NOCOUNT ON statement as the first statement in the body of the procedure. (она должна следовать сразу за ключевым словом AS).That is, place it just after the AS keyword. В этом случае отключаются сообщения, отправляемые SQL ServerSQL Server клиенту после выполнения любых инструкций SELECT, INSERT, UPDATE, MERGE и DELETE.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. При этом для ясности создаются минимальные выходные данные.This keeps the output generated to a minumum for clarity. Но на доступном сегодня оборудовании не наблюдается ощутимого повышения производительности.There is no measurable performance benefit however on todays hardware. Дополнительные сведения см. в разделе SET NOCOUNT (Transact-SQL).For information, see SET NOCOUNT (Transact-SQL).

  • При создании или упоминании объектов базы данных в процедуре используйте имена схем.Use schema names when creating or referencing database objects in the procedure. Отсутствие необходимости поиска в нескольких схемах экономит время обработки, затрачиваемое компонентом Компонент Database EngineDatabase Engine на разрешение имен объектов.It takes less processing time for the Компонент Database EngineDatabase Engine to resolve object names if it does not have to search multiple schemas. Кроме того, предотвращаются проблемы с разрешениями и доступом, вызываемые назначением схемы по умолчанию для пользователя, когда объекты создаются без указания схемы.It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema.

  • Не используйте функции-оболочки для столбцов, указанных в предложениях WHERE и JOIN.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. В таком случае столбцы становятся недетерминированными, и обработчик запросов не может использовать индексы.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Не используйте скалярные функции в инструкциях SELECT, возвращающих множество строк данных.Avoid using scalar functions in SELECT statements that return many rows of data. Поскольку скалярная функция должна применяться к каждой строке, инструкция будет выполняться как обработка на уровне строк, что приводит к снижению производительности.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Не используйте SELECT *.Avoid the use of SELECT *. Вместо этого указывайте имена нужных столбцов.Instead, specify the required column names. Это предотвращает некоторые ошибки компонента Компонент Database EngineDatabase Engine, которые останавливают выполнение процедуры.This can prevent some Компонент Database EngineDatabase Engine errors that stop procedure execution. Например, инструкция SELECT *, возвращающая данные из таблицы с 12 столбцами, а затем вставляющая эти данные во временную таблицу с 12 столбцами, выполняется успешно, пока не изменится число или порядок столбцов в любой из этих таблиц.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • Не выполняйте обработку или передачу слишком большого объема данных.Avoid processing or returning too much data. Как можно раньше ограничивайте область результатов в коде процедуры, чтобы все последующие операции, выполняемые процедурой, работали с минимально возможным набором данных.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Отправляйте в клиентское приложение только необходимые данные.Send just the essential data to the client application. Это более эффективно, чем передача дополнительных данных по сети, при которой клиентскому приложению приходится обрабатывать необоснованно большие результирующие наборы.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Используйте явные транзакции, указывая ключевые слова BEGIN/COMMIT TRANSACTION, и по возможности сокращайте транзакции.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Длинные транзакции увеличивают время блокировки записей и повышают шанс возникновения взаимоблокировок.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • Используйте функцию Transact-SQLTransact-SQL TRY…CATCH для обработки ошибок в пределах процедуры.Use the Transact-SQLTransact-SQL TRY...CATCH feature for error handling inside a procedure. В конструкцию TRY…CATCH можно инкапсулировать весь блок инструкций Transact-SQLTransact-SQL.TRY...CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. Это не только снижает расход ресурсов, но также повышает точность отчетов об ошибках и значительно сокращает труд программиста.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • Используйте ключевое слово DEFAULT для всех столбцов таблицы, на которые ссылаются инструкции Transact-SQLTransact-SQL CREATE TABLE и ALTER TABLE в тексте процедуры.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. Это исключает передачу значений NULL в столбцы, которые не допускают значений NULL.This prevents passing NULL to columns that do not allow null values.

  • Используйте ключевые слова NULL и NOT NULL для каждого столбца во временной таблице.Use NULL or NOT NULL for each column in a temporary table. Если атрибуты NULL или NOT NULL не указаны в инструкции CREATE TABLE или ALTER TABLE, то способ назначения этих атрибутов столбцам компонентом Компонент Database EngineDatabase Engine определяется параметрами ANSI_DFLT_ON и ANSI_DFLT_OFF.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Компонент Database EngineDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. Если в контексте соединения выполняется процедура, где значения этих параметров отличаются от значений в соединении, где была создана процедура, то столбцы таблицы, созданной для второго соединения, могут отличаться по признаку допустимости значений NULL и работать иначе.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. Если атрибут NULL или NOT NULL явно задан для каждого столбца, то временные таблицы создаются с одним и тем же признаком допустимости значений NULL во всех соединениях, где выполняется процедура.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Используйте инструкции изменения, которые преобразуют значения NULL и исключают из запросов строки, содержащие значения NULL.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Учтите, что в Transact-SQLTransact-SQL значение NULL не означает пустое значение или отсутствие значения.Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. Это заполнитель для неизвестного значения, который может вызвать непредвиденные результаты, особенно в случае запроса результирующих наборов или использования функций AGGREGATE.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Используйте оператор UNION ALL вместо операторов UNION и OR, если нет необходимости получить уникальные значения.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. Для оператора UNION ALL требуется меньше затрат на обработку, поскольку из результирующего набора не исключаются повторы.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

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

Стандартный максимальный размер процедуры не установлен.There is no predefined maximum size of a procedure.

Переменные, указанные в процедуре, могут определяться пользователем или быть системными, такими как @@SPID.Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

При выполнении процедуры в первый раз она компилируется, при этом определяется оптимальный план получения данных.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. При последующих вызовах процедуры можно снова использовать уже созданный план, если он еще находится в кэше планов компонента Компонент Database EngineDatabase Engine.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Компонент Database EngineDatabase Engine.

Процедуры могут выполняться автоматически при запуске SQL ServerSQL Server.One or more procedures can execute automatically when SQL ServerSQL Server starts. Они должны быть созданы системным администратором в базе данных master и выполняться в контексте предопределенной роли сервера sysadmin в фоновом процессе.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. Они не могут иметь ни входных, ни выходных параметров.The procedures cannot have any input or output parameters. Дополнительные сведения см. в разделе Выполнение хранимых процедур.For more information, see Execute a Stored Procedure.

Процедуры называются вложенными, если одна процедура вызывает другую или выполняет управляемый код по ссылке на подпрограмму, тип или статистическое выражение среды CLR.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. Процедуры и ссылки на управляемый код могут быть вложены не более чем на 32 уровня.Procedures and managed code references can be nested up to 32 levels. Уровень вложенности увеличивается на единицу, когда вызванная процедура или управляемый код начинает выполняться, и уменьшается на единицу, когда их выполнение заканчивается.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Методы, вызываемые из управляемого кода, не учитываются в этом ограничении.Methods invoked from within the managed code do not count against the nesting level limit. Однако когда хранимая процедура CLR выполняет операции доступа к данным через управляемый поставщик SQL Server, при переходе из управляемого кода в SQL добавляется дополнительный уровень вложенности.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

Если уровень вложенности превышает максимальное значение, вся цепочка вызовов заканчивается ошибкой.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. Получить уровень вложенности текущей выполняемой хранимой процедуры можно через функцию @@NESTLEVEL.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

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

При создании или изменении процедуры Компонент Database EngineDatabase Engine компонент Transact-SQLTransact-SQL сохраняет значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS.The Компонент Database EngineDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. Эти первоначальные значения используются при выполнении процедуры.These original settings are used when the procedure is executed. Таким образом, пока процедура выполняется, любые значения SET QUOTED_IDENTIFIER и SET ANSI_NULLS, задаваемые во время клиентского сеанса, не учитываются.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Другие параметры SET, такие как SET ARITHABORT, SET ANSI_WARNINGS или SET ANSI_PADDINGS, при создании или изменении процедуры не сохраняются.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. Если логика процедуры зависит от конкретного значения параметра, включите в начало процедуры инструкцию SET, чтобы гарантировать нужное значение.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. Если инструкция SET выполняется из процедуры, то значение действует только до завершения процедуры.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. После этого оно принимает прежнее значение, которое имело место при вызове процедуры.The setting is then restored to the value the procedure had when it was called. Это позволяет клиентам задавать нужные им параметры без влияния на логику процедуры.This enables individual clients to set the options they want without affecting the logic of the procedure.

Внутри процедуры может быть указана любая инструкция SET, за исключением SET SHOWPLAN_TEXT и SET SHOWPLAN_ALL.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. Эти инструкции могут встречаться только в пакете.These must be the only statements in the batch. Выбранный параметр SET остается в силе до завершения процедуры, после чего восстанавливает прежнее значение.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

Примечание

Значение SET ANSI_WARNINGS не учитывается при передаче параметров процедуре или определяемой пользователем функции, а также при объявлении и задании переменных в инструкции пакета.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

ОграниченияLimitations and Restrictions

Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQLTransact-SQL в одном пакете.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

Следующие инструкции нельзя использовать нигде в тексте хранимой процедуры.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE или ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE или ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE или ALTER VIEWCREATE or ALTER VIEW USE database_nameUSE database_name
CREATE или ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

Процедура может ссылаться на таблицы, которые еще не существуют.A procedure can reference tables that do not yet exist. Во время создания хранимой процедуры выполняется только проверка синтаксиса.At creation time, only syntax checking is performed. Процедура не компилируется до первого выполнения.The procedure is not compiled until it is executed for the first time. Ссылки на все упоминаемые в процедуре объекты разрешаются только во время компиляции.Only during compilation are all objects referenced in the procedure resolved. Таким образом, ничто не мешает создать синтаксически правильную процедуру, ссылающуюся на несуществующие таблицы, однако если эти таблицы отсутствуют во время выполнения хранимой процедуры, она завершится с ошибкой.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

Имя функции нельзя указать в качестве значения по умолчанию для параметра или в качестве значения, передаваемого для параметра во время выполнения процедуры.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. Однако функцию можно передать как переменную, как показано в следующем примере.However, you can pass a function as a variable as shown in the following example.

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

Если процедура вносит изменения на удаленном экземпляре SQL ServerSQL Server, то откат этих изменений будет невозможен.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. Удаленные процедуры не участвуют в транзакциях.Remote procedures do not take part in transactions.

Чтобы компонент Компонент Database EngineDatabase Engine правильно выбрал перегруженную в .NET Framework версию метода, в предложении EXTERNAL NAME необходимо указывать метод следующим образом.For the Компонент Database EngineDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • Он должен быть объявлен как статический метод.Be declared as a static method.

  • Он должен принимать то же количество параметров, что и процедура.Receive the same number of parameters as the number of parameters of the procedure.

  • Типы параметров метода должны быть совместимы с типами данных соответствующих параметров процедуры SQL ServerSQL Server.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. Сведения о соответствии между типами данных SQL ServerSQL Server и .NET Framework.NET Framework см. в разделе Сопоставление данных параметров CLR.For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

МетаданныеMetadata

В следующей таблице перечислены представления каталога и динамические административные представления, которые могут использоваться для получения сведений о хранимых процедурах.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

ПредставлениеView ОписаниеDescription
sys.sql_modulessys.sql_modules Возвращает определение процедуры Transact-SQLTransact-SQL.Returns the definition of a Transact-SQLTransact-SQL procedure. Текст процедуры, созданной с параметром ENCRYPTION, нельзя увидеть при помощи представления каталога sys.sql_modules.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules Возвращает сведения о процедуре CLR.Returns information about a CLR procedure.
sys.parameterssys.parameters Возвращает сведения о параметрах, которые определены в процедуреReturns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities Возвращает объекты, на которые ссылается процедура.Returns the objects that are referenced by a procedure.

Для оценки размера скомпилированной процедуры следует использовать следующие счетчики системного монитора.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

Имя объекта системного монитораPerformance Monitor object name Имя счетчика системного монитораPerformance Monitor Counter name
SQLServer: объект Plan CacheSQLServer: Plan Cache Object Коэффициент попадания в кэшCache Hit Ratio
Страницы кэшаCache Pages
Счетчик объектов в кэше*Cache Object Counts*

*Эти счетчики доступны для разных категорий объектов кэша, включая нерегламентированные запросы Transact-SQLTransact-SQL, подготовленные запросы Transact-SQLTransact-SQL, процедуры, триггеры и т. д.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. Дополнительные сведения см. в статье SQL Server, объект Plan Cache.For more information, see SQL Server, Plan Cache Object.

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

РазрешенияPermissions

Требуется разрешение CREATE PROCEDURE на базу данных и разрешение ALTER на схему, в которой создается процедура, либо членство в предопределенной роли базы данных db_ddladmin.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

Для хранимых процедур CLR пользователь должен владеть сборкой, на которую ссылается предложение EXTERNAL NAME, или иметь разрешение REFERENCES на эту сборку.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE и таблицы, оптимизированные для памятиCREATE PROCEDURE and Memory-Optimized Tables

Доступ к оптимизированным для памяти таблицам можно осуществлять из традиционных и скомпилированных в собственном коде хранимых процедур.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. В большинстве случаев использование собственных процедур является более эффективным способом.Native procedures are in most cases the more efficient way. Дополнительные сведения см. в статье Хранимые процедуры, скомпилированные в собственном коде.For more information, see Natively Compiled Stored Procedures.

В следующем примере показано создание скомпилированной в собственном коде хранимой процедуре, имеющей доступ к таблице, оптимизированной для памяти dbo.Departments.The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  
  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO  

Процедуру, созданную без параметра NATIVE_COMPILATION, нельзя преобразовать в хранимую процедуру, скомпилированную в собственном коде.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

Описание возможностей программирования в откомпилированных в собственном коде хранимых процедурах, поддерживаемой контактной зоны запроса и операторов см. в разделе Поддерживаемые функции для модулей, скомпилированных в собственном коде T-SQL.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

ПримерыExamples

КатегорияCategory Используемые элементы синтаксисаFeatured syntax elements
Базовый синтаксисBasic Syntax CREATE PROCEDURECREATE PROCEDURE
Передача параметровPassing parameters @parameter
   • значение по умолчанию   • = default
   • OUTPUT   • OUTPUT
   • тип возвращающего табличное значение параметра   • table-valued parameter type
   • CURSOR VARYING   • CURSOR VARYING
Изменение данных с помощью хранимой процедурыModifying data by using a stored procedure UPDATEUPDATE
Обработка ошибокError Handling TRY...CATCHTRY...CATCH
Запутывание определений процедурObfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
Принудительная перекомпиляция хранимой процедурыForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
Задание контекста безопасностиSetting the Security Context EXECUTE ASEXECUTE AS

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

В примерах из этого раздела показаны основные возможности инструкции CREATE PROCEDURE с применением минимально необходимого синтаксиса.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. Создание простой процедуры Transact-SQLCreating a simple Transact-SQL procedure

В следующем примере создается хранимая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов в базе данных AdventureWorks2012AdventureWorks2012.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. Эта процедура не использует параметры.This procedure does not use any parameters. Далее в примере рассматриваются три метода выполнения процедуры.The example then demonstrates three methods of executing the procedure.

CREATE PROCEDURE HumanResources.uspGetAllEmployees  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment;  
GO  
  
SELECT * FROM HumanResources.vEmployeeDepartment;  

Процедуру uspGetEmployees можно выполнять следующими способами.The uspGetEmployees procedure can be executed in the following ways:

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

Б.B. Возвращение более чем одного результирующего набораReturning more than one result set

Следующая процедура возвращает два результирующих набора.The following procedure returns two result sets.

CREATE PROCEDURE dbo.uspMultipleResults   
AS  
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;  
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;  
GO  

В.C. Создание хранимой процедуры CLRCreating a CLR stored procedure

В следующем примере создается процедура GetPhotoFromDB, ссылающаяся на метод GetPhotoFromDB класса LargeObjectBinary из сборки HandlingLOBUsingCLR.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Перед созданием процедуры сборка HandlingLOBUsingCLR регистрируется в локальной базе данных.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

Применимо к: SQL Server 2008SQL Server 2008 и выше, База данных SQLSQL Database (если используется сборка, созданная с помощью assembly_bits).Applies to: SQL Server 2008SQL Server 2008 and later, База данных SQLSQL Database (if using an assembly created from assembly_bits.

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  

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

Примеры в этом разделе показывают, как использовать входные и выходные параметры для передачи значений в хранимую процедуру и возврата значений из нее.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

Г.D. Создание простой процедуры со входными параметрамиCreating a procedure with input parameters

В следующем примере показано создание хранимой процедуры, которая возвращает сведения об определенном сотруднике по переданным значениям фамилии и имени сотрудника.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. Эта процедура принимает только полные соответствия передаваемым параметрам.This procedure accepts only exact matches for the parameters passed.

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 можно выполнять следующими способами.The uspGetEmployees procedure can be executed in the following ways:

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

Д.E. Использование процедуры с параметрами-шаблонамиUsing a procedure with wildcard parameters

В следующем примере показано создание хранимой процедуры, которая возвращает сведения о сотрудниках по переданным полным или частичным значениям имени и фамилии.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. Эта процедура ищет соответствие полученным параметрам, а если параметры не предоставлены, то используется шаблон, заданный по умолчанию (фамилии, начинающиеся с буквы D).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).

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;  

Процедуру uspGetEmployees2 можно выполнять во многих сочетаниях.The uspGetEmployees2 procedure can be executed in many combinations. Здесь показаны лишь некоторые из возможных сочетаний.Only a few possible combinations are shown here.

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

Е.F. Использование выходных параметров (OUTPUT)Using OUTPUT parameters

В следующем примере создается процедура uspGetList.The following example creates the uspGetList procedure. Эта процедура возвращает список товаров, цена на которые не превышает указанный предел.This procedures returns a list of products that have prices that do not exceed a specified amount. Данный пример поясняет использование нескольких инструкций SELECT и нескольких параметров OUTPUT.The example shows using multiple SELECT statements and multiple OUTPUT parameters. Параметры OUTPUT предоставляют внешней процедуре, пакету или нескольким инструкциям Transact-SQLTransact-SQL доступ к значениям, заданным во время выполнения процедуры.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 WorksAdventure Works список товаров (велосипедов) стоимостью менее $700.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. Параметры OUTPUT @Cost и @ComparePrices используются с языком управления выполнением для вывода информации в окне Сообщения.The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

Примечание

Переменная OUTPUT должна быть определена при создании процедуры и при использовании переменной.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. Имена параметра и переменной могут быть разными, однако типы данных и порядок расположения параметров должны совпадать, если только не используется @ListPrice = variable.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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)))+'.';  

Частичный результирующий набор:Here is the partial result set:

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.

Ж.G. Использование возвращающих табличные значения параметровUsing a Table-Valued Parameter

В следующем примере показано использование возвращающего табличное значение параметра для вставки в таблицу нескольких строк.The following example uses a table-valued parameter type to insert multiple rows into a table. В примере создается тип параметра, объявляется табличная переменная для ссылки, заполняется список параметров, а затем значения передаются в хранимую процедуру.The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. Хранимая процедура использует эти значения для вставки в таблицу нескольких строк.The stored procedure uses the values to insert multiple rows into a table.

/* 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 [AdventureWorks2012].[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   
    [AdventureWorks2012].[Person].[StateProvince];  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
З.H. Использование параметра-курсора OUTPUTUsing an OUTPUT cursor parameter

В следующем примере используется параметр курсора OUTPUT для возврата курсора, локального относительно процедуры, в вызывающий пакет, процедуру или триггер.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

Сначала следует создать процедуру, объявляющую и открывающую курсор для таблицы Currency:First, create the procedure that declares and then opens a cursor on the Currency table:

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  

Затем выполним пакет, в котором объявляется локальная переменная-курсор, выполняется процедура, назначающая курсор локальной переменной, и извлекаются строки из курсора.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

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

Изменение данных с помощью хранимой процедурыModifying Data by using a Stored Procedure

Примеры в этом разделе показывают, как производится вставка или изменение данных в таблицах и представлениях путем включения инструкции языка DML в определение процедуры.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

И.I. Использование UPDATE в хранимой процедуреUsing UPDATE in a stored procedure

В следующем примере инструкция UPDATE используется в хранимой процедуре.The following example uses an UPDATE statement in a stored procedure. Процедура принимает один входной параметр @NewHours и один выходной параметр @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. Значение параметра @NewHours используется в инструкции UPDATE для обновления столбца VacationHours в таблице HumanResources.Employee.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. Выходной параметр @RowCount используется для возврата значения числа задействованных строк в локальную переменную.The @RowCount output parameter is used to return the number of rows affected to a local variable. Выражение CASE используется в предложении SET для условного определения значения, которое задано для столбца VacationHours.A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. Если для сотрудника применяется почасовая ставка оплаты (SalariedFlag = 0), то в столбце VacationHours устанавливается текущее количество часов плюс значение, заданное в @NewHours. В противном случае в столбце VacationHours указывается значение, заданное в @NewHours.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

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;  

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

Примеры в этом разделе показывают, как производится обработка ошибок, которые могут возникнуть при выполнении хранимых процедур.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

К.J. Использование конструкции TRY...CATCHUsing TRY...CATCH

В следующем примере конструкция TRY…CATCH используется для возврата сведений об ошибках во время выполнения хранимой процедуры.The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.

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;  

Запутывание определений процедурObfuscating the Procedure Definition

Примеры в этом разделе показывают, как применить запутывание к определению хранимой процедуры.Examples in this section show how to obfuscate the definition of the stored procedure.

Л.K. Использование параметра WITH ENCRYPTIONUsing the WITH ENCRYPTION option

В следующем примере создается процедура HumanResources.uspEncryptThis.The following example creates the HumanResources.uspEncryptThis procedure.

Применимо к: SQL Server 2008SQL Server 2008 и выше, база данных SQL.Applies to: SQL Server 2008SQL Server 2008 and later, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

Параметр WITH ENCRYPTION запутывает определение процедуры при запросе системного каталога или использовании функций метаданных, как показано в следующих примерах.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Запустите sp_helptext.Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

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

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

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

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

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

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

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

В примерах этого раздела показано использование предложения WITH RECOMPILE для принудительной перекомпиляции процедуры при каждом ее выполнении.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

М.L. Использование параметра WITH RECOMPILEUsing the WITH RECOMPILE option

Предложение WITH RECOMPILE полезно, если передаваемые в процедуру параметры являются нетипичными или если новый план выполнения процедуры не следует кэшировать или хранить в памяти.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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;  

Задание контекста безопасностиSetting the Security Context

В примерах этого раздела предложение EXECUTE AS используется для задания контекста безопасности, в котором выполняется хранимая процедура.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

Н.M. Использование предложения EXECUTE ASUsing the EXECUTE AS clause

Следующий пример показывает использование предложения EXECUTE AS для указания контекста безопасности, в котором может выполняться процедура.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. В данном случае параметр CALLER указывает, что процедура может выполняться в контексте вызывающего ее пользователя.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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  

О.N. Создание пользовательских наборов разрешенийCreating custom permission sets

В следующем примере предложение EXECUTE AS используется для создания пользовательских разрешений для операции базы данных.The following example uses EXECUTE AS to create custom permissions for a database operation. Некоторые операции (например, TRUNCATE TABLE) не имеют предоставляемых разрешений.Some operations such as TRUNCATE TABLE, do not have grantable permissions. Включив инструкцию TRUNCATE TABLE в хранимую процедуру и указав, что эта процедура должна выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE на эту процедуру.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

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

Примеры: Azure Synapse Analytics (хранилище данных SQL)Azure Synapse Analytics (SQL DW) и Параллельное хранилище данныхParallel Data WarehouseExamples: Azure Synapse Analytics (хранилище данных SQL)Azure Synapse Analytics (SQL DW) and Параллельное хранилище данныхParallel Data Warehouse

П.O. Создание хранимой процедуры, которая выполняет инструкцию SELECTCreate a Stored Procedure that runs a SELECT statement

В этом примере показан основной синтаксис для создания и выполнения процедуры.This example shows the basic syntax for creating and running a procedure. При выполнении пакета CREATE PROCEDURE должна быть первой инструкцией.When running a batch, CREATE PROCEDURE must be the first statement. Например, чтобы создать следующую хранимую процедуру в AdventureWorksPDW2012AdventureWorksPDW2012, сначала следует задать контекст базы данных, а затем выполнить инструкцию CREATE PROCEDURE.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  
  
--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  
GO
  
--Show 10 Top Resellers  
EXEC Get10TopResellers;  

См. такжеSee Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
Язык управления потоком (Transact-SQL) Control-of-Flow Language (Transact-SQL)
Курсоры Cursors
Типы данных (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
Хранимые процедуры (ядро СУБД) Stored Procedures (Database Engine)
sp_procoption (Transact-SQL) sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures (Transact-SQL) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
Создание хранимой процедуры Create a Stored Procedure
Использование параметров, возвращающих табличные значения (ядро СУБД) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)