Инструкция ALTER PROCEDURE (Transact-SQL)

Статья
06/18/2013

Изменяет ранее созданную процедуру с помощью инструкции CREATE PROCEDURE в SQL Server 2012.

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

Синтаксис

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

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

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

Аргументы

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

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

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

@ parameter
Параметр в процедуре. Можно указать до 2 100 аргументов.
[ type_schema_name**.** ] data_type
Тип данных аргумента и схема, которой он принадлежит.

Дополнительные сведения об ограничениях типов данных см. в разделе CREATE PROCEDURE (Transact-SQL).
VARYING
Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот параметр формируется динамически хранимой процедурой, и его содержимое может меняться. Область применения — только аргументы курсора. Этот параметр недопустим для процедур CLR.
default
Значение по умолчанию для аргумента.
OUT | OUTPUT
Указывает, что параметр является выходным.
READONLY
Указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является возвращающим табличное значение типом, то должно быть указано ключевое слово READONLY.
RECOMPILE
Указывает, что компонент Компонент Database Engine не кэширует план этой процедуры и она перекомпилируется во время выполнения.

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

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

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

Примечание
Во время обновления компонент Компонент Database Engine использует затемненные комментарии, которые хранятся в таблице sys.sql_modules для повторного создания зашифрованных процедур.

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

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

Примечание

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

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

Примечание

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

Примечание
Этот параметр недоступен в автономной базе данных.

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

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

Хранимые процедуры Transact-SQL нельзя преобразовать в хранимые процедуры CLR и наоборот.

Инструкция ALTER PROCEDURE не изменяет разрешения и не влияет на хранимые процедуры и триггеры. Тем не менее при изменении в хранимую процедуру включаются текущие значения для параметров сеанса QUOTED_IDENTIFIER и ANSI_NULLS. Если при создании хранимой процедуры использовались другие значения параметров, ее поведение может измениться.

Если предыдущее определение процедуры было создано с параметрами WITH ENCRYPTION или WITH RECOMPILE, эти параметры будут включены только в том случае, если они указаны в инструкции ALTER PROCEDURE.

Дополнительные сведения о хранимых процедурах см. в разделе CREATE PROCEDURE (Transact-SQL).

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

Разрешения

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

Примеры

В следующем примере создается хранимая процедура uspVendorAllInfo. Эта хранимая процедура возвращает имена всех поставщиков, которые содержатся в базе данных Компания Adventure Works Cycles, товары, которые они производят, оценку кредитоспособности и доступность. После создания процедура изменяется и возвращает другой результирующий набор.

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

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

USE AdventureWorks2012;
GO
ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SET NOCOUNT ON;
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Rating' = CASE v.CreditRating 
        WHEN 1 THEN 'Superior'
        WHEN 2 THEN 'Excellent'
        WHEN 3 THEN 'Above average'
        WHEN 4 THEN 'Average'
        WHEN 5 THEN 'Below average'
        ELSE 'No rating'
        END
    , Availability = CASE v.ActiveFlag
        WHEN 1 THEN 'Yes'
        ELSE 'No'
        END
    FROM Purchasing.Vendor AS v 
    INNER JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO

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

Vendor Product name Rating Availability

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

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)