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

CREATE PROCEDURE (Transact-SQL)

  • Статья

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

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

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

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

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

Применимо для следующих объектов: SQL Server (с SQL Server 2008 по текущую версию), База данных SQL Azure (с первоначального выпуска по текущий выпуск).

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

Синтаксис

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

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

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

--SQL Server Natively Compiled Stored Procedure Syntax
CREATE { 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 } ]

-- Windows Azure SQL Database Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [ type_schema_name. ] data_type } 
    [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
AS { <sql_statement> [;][ ...n ] }
[;]
<procedure_option> ::= 
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

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

Аргументы

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

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

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

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

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

  • ; number

    Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

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

    Примечание

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

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

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

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

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

  • [ type_schema_name**.** ] data_type
    Тип данных параметра и схема, к которой принадлежит этот тип.

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

    • Все типы данных Transact-SQL можно использовать в качестве параметров.

    • Для создания возвращающих табличное значение параметров можно использовать определяемый пользователем табличный тип. Возвращающие табличное значение параметры могут быть только ВХОДНЫМИ и должны сопровождаться ключевым словом READONLY. Дополнительные сведения см. в разделе Использование параметров, возвращающих табличные значения (компонент Database Engine).

    • Типы данных cursor могут быть только ВЫХОДНЫМИ параметрами и должны сопровождаться ключевым словом VARYING.

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

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

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

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

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

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

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

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

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

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

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

  • ENCRYPTION

    Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

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

    Предложения SELF, OWNER и ‘user_name’ поддерживаются для In-Memory OLTP.

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

  • FOR REPLICATION

    Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

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

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

  • { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    Одна или несколько инструкций Transact-SQL, составляющих текст процедуры. Инструкции можно заключить в необязательные ключевые слова BEGIN и END. Дополнительные сведения см. далее в разделах «Рекомендации», «Общие замечания» и «Ограничения».

  • EXTERNAL NAME assembly_name**.class_name.**method_name

    Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

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

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

    Примечание

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

  • ATOMIC WITH

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

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

    Параметр XACT_ABORT по умолчанию имеет значение ON внутри блока ATOMIC и не может быть изменен. Параметр 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.

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

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

  • NATIVE_COMPILATION

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

  • SCHEMABINDING

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

  • LANGUAGE = [N] ‘language’

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

  • TRANSACTION ISOLATION LEVEL

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

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

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

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

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

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

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

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

  • DATEFIRST = number

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

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

  • DATEFORMAT = format

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

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

  • DELAYED_DURABILITY = { OFF | ON }

    Применимо для следующих объектов: с SQL Server 2014 по SQL Server 2014 включительно.

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

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

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

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

  • Начинайте текст процедуры с инструкции SET NOCOUNT ON (она должна следовать сразу за ключевым словом AS). В этом случае отключаются сообщения, отправляемые SQL Server клиенту после выполнения любых инструкций SELECT, INSERT, UPDATE, MERGE и DELETE. Устранение ненужной нагрузки на сеть повышает общую производительность базы данных и приложения. Дополнительные сведения см. в разделе SET NOCOUNT (Transact-SQL).

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

  • Не используйте функции-оболочки для столбцов, указанных в предложениях WHERE и JOIN. В таком случае столбцы становятся недетерминированными, и обработчик запросов не может использовать индексы.

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

  • Не используйте инструкцию SELECT *. Вместо этого указывайте имена нужных столбцов. Это предотвращает некоторые ошибки компонента Компонент Database Engine, которые останавливают выполнение процедуры. Например, инструкция SELECT *, возвращающая данные из таблицы с 12 столбцами, а затем вставляющая эти данные во временную таблицу с 12 столбцами, будет выполняться успешно, пока не изменится число или порядок столбцов в любой из этих таблиц.

  • Не выполняйте обработку или передачу слишком большого объема данных. Как можно раньше ограничивайте область результатов в коде процедуры, чтобы все последующие операции, выполняемые процедурой, работали с минимально возможным набором данных. Отправляйте в клиентское приложение только необходимые данные. Это более эффективно, чем передача дополнительных данных по сети, при которой клиентскому приложению приходится обрабатывать необоснованно большие результирующие наборы.

  • Используйте явные транзакции, указывая ключевые слова BEGIN/END 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, то способ назначения этих атрибутов столбцам компонентом Компонент Database Engine определяется параметрами ANSI_DFLT_ON и ANSI_DFLT_OFF. Если в контексте соединения выполняется процедура, где значения этих параметров отличаются от значений в соединении, где была создана процедура, то столбцы таблицы, созданной для второго соединения, могут отличаться по признаку допустимости значений NULL и работать иначе. Если атрибут NULL или NOT NULL явно задан для каждого столбца, то временные таблицы создаются с одним и тем же признаком допустимости значений NULL во всех соединениях, где выполняется процедура.

  • Используйте инструкции изменения, которые преобразуют значения NULL и исключают из запросов строки, содержащие значения NULL. Учтите, что в Transact-SQL значение NULL не означает пустое значение или отсутствие значения. Это заполнитель для неизвестного значения, который может вызвать непредвиденные результаты, особенно в случае запроса результирующих наборов или использования функций AGGREGATE.

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

Ограничения

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

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

CREATE AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT

CREATE DEFAULT

CREATE или ALTER TRIGGER

SET SHOWPLAN_XML

CREATE или ALTER FUNCTION

CREATE или ALTER VIEW

USE database_name

CREATE или ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

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

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

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

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

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

  • Он должен быть объявлен как статический метод.

  • Он должен принимать то же количество параметров, что и процедура.

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

Метаданные

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

Представление

Описание

sys.sql_modules

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

sys.assembly_modules

Возвращает сведения о процедуре CLR.

sys.parameters

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

sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities

Возвращает объекты, на которые ссылается процедура.

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

Имя объекта системного монитора

Имя счетчика системного монитора

SQLServer: объект Plan Cache

Коэффициент попадания в кэш

 

Страницы кэша

 

Счетчик объектов в кэше*

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

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

Разрешения

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

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

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

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

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

CREATE PROCEDURE 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

Для работы с таблицами, оптимизированными для памяти, используйте хранимые процедуры, скомпилированные в собственном коде. Дополнительные сведения см. в разделе In-Memory OLTP (оптимизация в памяти).

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

Процедуру, созданную без параметра NATIVE_COMPILATION, нельзя преобразовать в хранимую процедуру, скомпилированную в собственном коде. Инструкция ALTER PROCEDURE не поддерживается для хранимых процедур, скомпилированных в собственном коде.

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

Примеры

Категория

Используемые элементы синтаксиса

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

CREATE PROCEDURE

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

@parameter • = значение по умолчанию • OUTPUT • тип возвращающего табличное значение параметра • CURSOR VARYING

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

UPDATE

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

TRY…CATCH

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

WITH ENCRYPTION

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

WITH RECOMPILE

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

EXECUTE AS

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

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

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

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

IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

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;

Б.Возвращение более чем одного результирующего набора

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

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

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

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

Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

 
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

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

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

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

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

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

В.Использование выходных параметров (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.

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

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

/* 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

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

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

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

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

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

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 в определение процедуры.

А.Использование 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
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

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

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

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

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

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

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

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

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

GO
EXEC Production.uspDeleteWorkOrder 13;

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

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

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

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

DROP PROCEDURE Production.uspDeleteWorkOrder;

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

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

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

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

Применимо для следующих объектов: с SQL Server 2008 по SQL Server 2014 включительно.

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

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

Запустите sp_helptext.

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

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

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

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

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

definition

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

NULL

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

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

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

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

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

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

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

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

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

См. также

Справочник

Инструкция 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_references (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)

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

Курсоры

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

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

Использование параметров, возвращающих табличные значения (компонент Database Engine)