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

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

Синтаксические обозначения в 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
  Указывает результирующий набор, поддерживаемый в качестве выходного аргумента. Этот аргумент формируется динамически хранимой процедурой, и его содержимое может меняться. Область применения — только аргументы курсора.

• default
  Значение по умолчанию для параметра.

• OUT | OUTPUT
  Указывает, что параметр является выходным.

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

• RECOMPILE
  Указывает, что компонент SQL Server 2005 Database Engine не кэширует план этой процедуры и она перекомпилируется во время выполнения.

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

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

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

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

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

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

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

• AS
  Действия, которые выполняет хранимая процедура.

• <sql_statement>
  Любое количество инструкций Transact-SQL, составляющих тело хранимой процедуры. На процедуры накладываются некоторые ограничения. Дополнительные сведения см. в разделе «Ограничения <sql_statement>» в разделе CREATE PROCEDURE (Transact-SQL).

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

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

Замечания

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

Дополнительные сведения см. в подразделе «Примечания» в разделе CREATE PROCEDURE (Transact-SQL).

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

Разрешения

Необходимо разрешение ALTER на процедуру.

Примеры

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

USE AdventureWorks;
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 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

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

ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Credit 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.VendorID = pv.VendorID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';
GO

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

Vendor               Product name        Credit rating Availability
-------------------- ------------------- ------------- ------------
Proseware, Inc.      LL Crankarm         Average       No
Vision Cycles, Inc.  LL Crankarm         Superior      Yes

(2 row(s) affected)

См. также

Задания

Как изменить хранимую процедуру (среда SQL Server Management Studio)

Справочник

CREATE PROCEDURE (Transact-SQL)

Язык управления потоком (Transact-SQL)

Типы данных (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

EVENTDATA (Transact-SQL)

Функции (Transact-SQL)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

Представление каталога sys.sql_expression_dependencies (Transact-SQL)

Функция динамического управления sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

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

Пакеты

Курсоры (компонент Database Engine)

Использование переменных и параметров (компонент Database Engine)

Внесение изменений схем в базы данных публикаций

Другие ресурсы

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