CREATE PROCEDURE (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

Создает хранимую процедуру Transact-SQL или общеязыковой среды выполнения (CLR) в следующих компонентах: SQL Server, База данных SQL Azure и Система платформы аналитики (PDW). Хранимые процедуры похожи на процедуры из других языков программирования в том, что они могут:

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

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

Примечание.

В этом разделе рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция со средой CLR не применяется к базе данных SQL Azure.

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис Transact-SQL для хранимых процедур в SQL Server и Базе данных SQL Azure:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = 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 для хранимых процедур CLR:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ 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 для хранимых процедур, скомпилированных в собственном коде:

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 для хранимых процедур в Azure Synapse Analytics и Parallel Data Warehouse:

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Синтаксис Transact-SQL для хранимых процедур в Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Аргументы

OR ALTER

Применимо к: База данных SQL Azure, SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1).

Изменяет процедуру, если она уже существует.

schema_name

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

procedure_name

Имя процедуры. Имена процедур должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме.

Внимание

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

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

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

; номер

Применимо: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.

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

Примечание.

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

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

@parameter_name

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

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

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

[ type_schema_name. ] data_type

Тип данных параметра и схема, к которой принадлежит этот тип.

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

• Все типы данных Transact-SQL можно использовать в качестве параметров.
• Для создания возвращающих табличное значение параметров можно использовать определяемый пользователем табличный тип. Возвращающие табличное значение параметры могут быть только ВХОДНЫМИ и должны сопровождаться ключевым словом READONLY. Дополнительные сведения см. в статье Использование возвращающих табличные значения параметров (ядро СУБД)
• Типы данных cursor могут быть только ВЫХОДНЫМИ параметрами и должны сопровождаться ключевым словом VARYING.

Рекомендации по процедурам CLR

• Все типы данных SQL Server, имеющие эквивалент в управляемом коде, могут использоваться в качестве параметров. Дополнительные сведения о соответствии типов СРЕДЫ CLR и системных типов данных SQL Server см. в разделе "Сопоставление данных параметров CLR". Дополнительные сведения о системных типах данных SQL Server и их синтаксисе см. в разделе Типы данных (Transact-SQL).

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

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

VARYING

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

default

Значение по умолчанию для параметра. Если для некоторого параметра определено значение по умолчанию, то процедуру можно выполнить без указания значения этого параметра. Значение по умолчанию должно быть константой или может быть равно NULL. Значение константы может иметь вид шаблона, что позволяет использовать ключевое слово LIKE при передаче параметра в процедуру.

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

OUT | OUTPUT

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

READONLY

Указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является возвращающим табличное значение типом, то должно быть указано ключевое слово READONLY.

RECOMPILE

Показывает, что ядро СУБД не кэширует план запроса для этой процедуры, что вызывает ее компиляцию при каждом выполнении. Дополнительные сведения о причинах принудительной повторной компиляции см. в разделе Перекомпиляция хранимой процедуры. Этот параметр нельзя использовать, если указано предложение FOR REPLICATION, а также для процедур CLR.

Чтобы указать ядро СУБД отменить планы запросов для отдельных запросов в процедуре, используйте указание запроса RECOMPILE в определении запроса. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

ШИФРОВАНИЕ

Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий), База данных SQL Azure.

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

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

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

EXECUTE AS предложение

Определяет контекст безопасности, в котором должна быть выполнена процедура.

Для скомпилированных хранимых процедур, начиная с SQL Server 2016 (13.x) и в База данных SQL Azure, нет ограничений в предложении EXECUTE AS. В SQL Server 2014 (12.x) предложения SELF, OWNER и user_name поддерживаются с скомпилированных в собственном коде хранимых процедур.

Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).

FOR REPLICATION

Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий), База данных SQL Azure.

Указывает, что процедура создается для репликации. Следовательно, ее нельзя выполнять на подписчике. Процедура, созданная с параметром FOR REPLICATION, используется в качестве фильтра и выполняется только в процессе репликации. Параметры не могут быть объявлены, если указан параметр FOR REPLICATION. Параметр FOR REPLICATION нельзя указывать для процедур CLR. Параметр RECOMPILE не учитывается для процедур, созданных с параметром FOR REPLICATION.

Процедура FOR REPLICATION имеет тип объекта RF и sys.objects sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Одна или несколько инструкций Transact-SQL, составляющих текст процедуры. Инструкции можно заключить в необязательные ключевые слова BEGIN и END. Дополнительные сведения см. далее в разделах "Рекомендации", "Общие замечания" и "Ограничения".

EXTERNAL NAME assembly_name.class_name.method_name

Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

Указывает метод сборки платформа .NET Framework для ссылки на процедуру CLR. class_name должен быть допустимым идентификатором SQL Server и должен существовать в качестве класса в сборке. Если класс имеет полное имя пространства имен, использующее точку (.) для разделения частей пространства имен, имя класса должно быть разделено с помощью квадратных скобок ([]) или кавычки (""). Указанный метод класса должен быть статическим.

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

Примечание.

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

ATOMIC WITH

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Указывает атомарное выполнение хранимой процедуры. Изменения принимаются, либо все изменения откатываются с исключением. Блок ATOMIC WITH требуется для скомпилированных в собственном коде хранимых процедур.

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

Параметр XACT_ABORT по умолчанию имеет значение ON внутри атомарного блока и не может быть изменен. Параметр XACT_ABORT указывает, выполняет ли SQL Server автоматический откат текущей транзакции, если инструкция языка Transact-SQL вызывает ошибку во время выполнения.

Следующие параметры SET всегда имеют значение ON внутри блока ATOMIC. Их нельзя изменить.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

Параметры SET нельзя изменить внутри блоков ATOMIC. Параметры SET пользовательского сеанса не используются в области скомпилированных в собственном коде хранимых процедур. Эти параметры фиксируются во время компиляции.

Операции BEGIN, ROLLBACK и COMMIT нельзя использовать внутри атомарного блока.

Для каждой скомпилированной в собственном коде хранимой процедуры существует один блок ATOMIC. Блок входит во внешнюю область процедуры. Блоки не могут быть вложенными. Дополнительные сведения о скомпилированных в собственном коде хранимых процедурах см. в разделе Хранимые процедуры, скомпилированные в собственном коде.

NULL | NOT NULL

Определяет, допустимы ли для параметра значения NULL. Значение по умолчанию — NULL.

NATIVE_COMPILATION

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Указывает, что процедура компилируется в собственном режиме. NATIVE_COMPILATION, SCHEMABINDING и EXECUTE AS можно указывать в любом порядке. Дополнительные сведения см. в статье Хранимые процедуры, скомпилированные в собственном коде.

SCHEMABINDING

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Гарантирует, что таблицы, на которые ссылается процедура, нельзя удалить или изменить. SCHEMABINDING требуется для хранимых процедур, скомпилированных в собственном коде. (Дополнительные сведения см. в разделе Скомпилированные хранимые процедуры в собственном коде.) Ограничения SCHEMABINDING те же, что и для определяемых пользователем функций. Дополнительные сведения см. в подразделе SCHEMABINDING раздела CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

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

TRANSACTION ISOLATION LEVEL

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

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

Дополнительные сведения об этих параметрах см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READ

Указывает, что инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы. Если другая транзакция изменяет данные, считанные текущей транзакцией, текущая транзакция завершится с ошибкой.

SERIALIZABLE

Указывает следующее.

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

МОМЕНТАЛЬНЫЙ СНИМОК

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

DATEFIRST = number

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Указывает первый день недели в виде числа от 1 до 7. DATEFIRST — необязательный параметр. Если он не указан, то значение выводится из указанного языка.

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

DATEFORMAT = format

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Указывает порядок составляющих даты (месяца, дня и года) для интерпретации символьных строк date, smalldatetime, datetime, datetime2 и datetimeoffset. DATEFORMAT — необязательный параметр. Если он не указан, то значение выводится из указанного языка.

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

DELAYED_DURABILITY = { OFF | ON }

Область применения: SQL Server 2014 (12.x) и более поздние версии, а также База данных SQL Azure.

Фиксации транзакций SQL Server могут быть полностью устойчивыми, стандартными или отложенными устойчивыми.

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

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

Чтобы помочь вам приступить к работе, рассмотрим два простых примера. SELECT DB_NAME() AS ThisDB; возвращает имя текущей базы данных. Можно создать оболочку этой инструкции в хранимой процедуре, например:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

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

Немного более сложным вариантом является предоставление входного параметра для повышения уровня гибкости процедуры. Например:

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

Укажите идентификационный номер базы данных при вызове процедуры. Например, EXEC What_DB_is_that 2; возвращает tempdb.

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

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

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

• Начинайте текст процедуры с инструкции SET NOCOUNT ON (она должна следовать сразу за ключевым словом AS). Это отключает сообщения, отправляемые SQL Server обратно клиенту после выполнения инструкций SELECT, INSERT, UPDATE, MERGE и DELETE. При этом для ясности создаются минимальные выходные данные. Но на доступном сегодня оборудовании не наблюдается ощутимого повышения производительности. Дополнительные сведения см. в разделе SET NOCOUNT (Transact-SQL).
• При создании или упоминании объектов базы данных в процедуре используйте имена схем. Отсутствие необходимости поиска в нескольких схемах экономит время обработки, затрачиваемое ядром СУБД на разрешение имен объектов. Кроме того, предотвращаются проблемы с разрешениями и доступом, вызываемые назначением схемы по умолчанию для пользователя, когда объекты создаются без указания схемы.
• Не используйте функции-оболочки для столбцов, указанных в предложениях WHERE и JOIN. В таком случае столбцы становятся недетерминированными, и обработчик запросов не может использовать индексы.
• Не используйте скалярные функции в инструкциях SELECT, возвращающих множество строк данных. Поскольку скалярная функция должна применяться к каждой строке, инструкция будет выполняться как обработка на уровне строк, что приводит к снижению производительности.
• Не используйте SELECT *. Вместо этого указывайте имена нужных столбцов. Это может предотвратить некоторые ядро СУБД ошибки, которые останавливают выполнение процедуры. Например, инструкция SELECT *, возвращающая данные из таблицы с 12 столбцами, а затем вставляющая эти данные во временную таблицу с 12 столбцами, выполняется успешно, пока не изменится число или порядок столбцов в любой из этих таблиц.
• Не выполняйте обработку или передачу слишком большого объема данных. Как можно раньше ограничивайте область результатов в коде процедуры, чтобы все последующие операции, выполняемые процедурой, работали с минимально возможным набором данных. Отправляйте в клиентское приложение только необходимые данные. Это более эффективно, чем передача дополнительных данных по сети, при которой клиентскому приложению приходится обрабатывать необоснованно большие результирующие наборы.
• Используйте явные транзакции, указывая ключевые слова BEGIN/COMMIT TRANSACTION, и по возможности сокращайте транзакции. Длинные транзакции увеличивают время блокировки записей и повышают шанс возникновения взаимоблокировок.
• Используйте функцию Transact-SQL TRY…CATCH для обработки ошибок в пределах процедуры. В конструкцию TRY…CATCH можно инкапсулировать весь блок инструкций Transact-SQL. Это не только снижает расход ресурсов, но также повышает точность отчетов об ошибках и значительно сокращает труд программиста.
• Используйте ключевое слово DEFAULT для всех столбцов таблицы, на которые ссылаются инструкции Transact-SQL CREATE TABLE и ALTER TABLE в тексте процедуры. Это исключает передачу значений NULL в столбцы, которые не допускают значений NULL.
• Используйте ключевые слова NULL и NOT NULL для каждого столбца во временной таблице. Если атрибуты NULL или NOT NULL не указаны в инструкции CREATE TABLE или ALTER TABLE, то способ назначения этих атрибутов столбцам ядром СУБД определяется параметрами ANSI_DFLT_ON и ANSI_DFLT_OFF. Если в контексте соединения выполняется процедура, где значения этих параметров отличаются от значений в соединении, где была создана процедура, то столбцы таблицы, созданной для второго соединения, могут отличаться по признаку допустимости значений NULL и работать иначе. Если атрибут NULL или NOT NULL явно задан для каждого столбца, то временные таблицы создаются с одним и тем же признаком допустимости значений NULL во всех соединениях, где выполняется процедура.
• Используйте инструкции изменения, которые преобразуют значения NULL и исключают из запросов строки, содержащие значения NULL. Учтите, что в Transact-SQL значение NULL не означает пустое значение или отсутствие значения. Это заполнитель для неизвестного значения, который может вызвать непредвиденные результаты, особенно в случае запроса результирующих наборов или использования функций AGGREGATE.
• Используйте оператор UNION ALL вместо операторов UNION и OR, если нет необходимости получить уникальные значения. Для оператора UNION ALL требуется меньше затрат на обработку, так как из результирующего набора не исключаются повторы.

Замечания

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

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

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

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

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

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

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

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

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

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

Примечание.

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

ограничения

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

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

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

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

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

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

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

Чтобы ядро СУБД ссылаться на правильный метод при перегрузке в платформа .NET Framework, метод, указанный в предложении EXTERNAL NAME, должен иметь следующие характеристики:

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

Метаданные

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

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

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

Имя объекта системного монитора	Имя счетчика системного монитора
SQL Server, объект Plan Cache	Коэффициент попадания в кэш
	Страницы кэша
	Счетчик объектов в кэше 1

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

Разрешения

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

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

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

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

В следующем примере показано создание скомпилированной в собственном коде хранимой процедуре, имеющей доступ к таблице, оптимизированной для памяти 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, нельзя преобразовать в хранимую процедуру, скомпилированную в собственном коде.

Описание возможностей программирования в откомпилированных в собственном коде хранимых процедурах, поддерживаемой контактной зоны запроса и операторов см. в разделе Поддерживаемые функции для модулей, скомпилированных в собственном коде T-SQL.

Примеры

Категория	Используемые элементы синтаксиса
Базовый синтаксис	СОЗДАТЬ ПРОЦЕДУРУ
Передача параметров	@parameter = default ВЫПУСК Тип параметра с табличным значением CURSOR VARYING
Изменение данных с помощью хранимой процедуры	UPDATE
Обработка ошибок	TRY...CATCH
Запутывание определений процедур	WITH ENCRYPTION
Принудительная перекомпиляция хранимой процедуры	WITH RECOMPILE
Задание контекста безопасности	EXECUTE AS

Основной синтаксис

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

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

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

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

SELECT * FROM HumanResources.vEmployeeDepartment;

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

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

B. Возвращение нескольких результирующих наборов

Следующая процедура возвращает два результирующих набора.

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

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

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

Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL (если используется сборка, созданная из 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

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

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

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

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

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

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 можно выполнять во многих сочетаниях. Здесь показаны лишь некоторые из возможных сочетаний.

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)

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

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.

G. Использование возвращающего табличное значение параметра

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

/* 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 [AdventureWorks2022].[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
    [AdventureWorks2022].[Person].[StateProvince];

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

H. Использование параметра-курсора OUTPUT

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

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

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

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

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

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

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

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

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

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

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

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

J. Использование TRY...CATCH

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

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;
GO
/* 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;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Обфускация определения процедуры

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

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

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

Применимо: SQL Server 2008 (10.0.x) и более поздних версий и База данных SQL Azure.

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

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

Запустите sp_helptext.

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Вот результирующий набор.

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

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

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

Вот результирующий набор.

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

Примечание.

Azure Synapse Analytics не поддерживает системную хранимую процедуру sp_helptext. Вместо нее используйте представление каталога объектов sys.sql_modules.

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

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

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

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

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;

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

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

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

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

CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name',
      v.CreditRating AS 'Rating',
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    ORDER BY v.Name ASC;
GO

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

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

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

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

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

В этом примере показан основной синтаксис для создания и выполнения процедуры. При выполнении пакета CREATE PROCEDURE должна быть первой инструкцией. Например, чтобы создать следующую хранимую процедуру в AdventureWorksPDW2022, сначала задайте контекст базы данных, а затем выполните инструкцию CREATE PROCEDURE.

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

См. также

• ALTER PROCEDURE (Transact-SQL)
• Язык управления потоком (Transact-SQL)
• Курсоры
• Типы данных (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• EXECUTE AS (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_expression_dependencies (Transact-SQL)
• sys.assembly_modules (Transact-SQL)
• sys.numbered_procedures (Transact-SQL)
• sys.numbered_procedure_parameters (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• Создание хранимой процедуры
• Использование параметров, возвращающих табличные значения (ядро СУБД)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)