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

Статья
03/07/2014

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

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

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

Синтаксис

--SQL Server 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 ]

--SQL Server 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 }
[;]

-- Windows Azure SQL Database Syntax 

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [type_schema_name. ] data_type } 
    [ VARYING ] [= default ] [ [ OUT [ PUT ] 
    ] [,...n ] 
[ WITH <procedure_option> [ , ...n ] ] 
AS 
     { <sql_statement> [...n ] }
[;]

<procedure_option> ::= 
    [ RECOMPILE ] 
    [ EXECUTE_AS_Clause ] 

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

Аргументы

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

Примечание

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

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)