ALTER PROCEDURE (Transact-SQL)

Artigo
03/07/2014

Modifica um procedimento criado anteriormente com a execução da instrução CREATE PROCEDURE no SQL Server.

Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Windows Azure (versão inicial até a versão atual).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL (Transact-SQL)

Sintaxe

--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 ] }

Argumentos

schema_name
O nome do esquema ao qual o procedimento pertence.
procedure_name
O nome do procedimento a ser alterado. Os nomes de procedimento devem estar de acordo com as regras para identificadores.
; number
Um inteiro opcional existente que é usado para agrupar procedimentos do mesmo nome, para que possam ser descartados juntos usando uma instrução DROP PROCEDURE.

Dica

Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.
@ parameter
Um parâmetro no procedimento. Podem ser especificados até 2.100 parâmetros.
[ type_schema_name**.** ] data_type
É o tipo de dados do parâmetro e o esquema ao qual ele pertence.

Para obter mais informações sobre restrições de tipos de dados, consulte CREATE PROCEDURE (Transact-SQL).
VARYING
Especifica o conjunto de resultados com suporte como um parâmetro de saída. Este parâmetro é construído dinamicamente pelo procedimento armazenado, e seu conteúdo pode variar. Aplica-se apenas a parâmetros de cursor. Esta opção não é válida para procedimentos CLR.
default
É um valor padrão para o parâmetro.
OUT | OUTPUT
Indica que o parâmetro é um parâmetro de retorno.
READONLY
Indica que o parâmetro não pode ser atualizado ou modificado no corpo do procedimento. Se o tipo de parâmetro for um tipo com valor de tabela, deverá ser especificado READONLY.
RECOMPILE
Indica que o Mecanismo de Banco de Dados não armazena em cache um plano para esse procedimento e o procedimento é recompilado em tempo de execução.
ENCRYPTION

Aplica-se a: SQL Server 2008 a SQL Server 2014.

Indica que o Mecanismo de Banco de Dados converterá o texto original da instrução ALTER PROCEDURE em um formato ofuscado. A saída do ofuscamento não é diretamente visível em quaisquer exibições de catálogo no SQL Server. Os usuários que não tiverem nenhum acesso a tabelas do sistema ou arquivos de banco de dados não poderão recuperar o texto ofuscado. Entretanto, o texto estará disponível para usuários privilegiados que puderem acessar as tabelas de sistema na porta DAC ou acessar diretamente os arquivos do banco de dados. Além disso, os usuários que podem anexar um depurador ao processo de servidor também podem recuperar o procedimento original da memória em tempo de execução. Para obter mais informações sobre como acessar os metadados do sistema, consulte Configuração de visibilidade de metadados.

Procedimentos criados com esta opção não podem ser publicados como parte de replicação do SQL Server.

Esta opção não pode ser especificada para procedimentos armazenados CLR (Common Language Runtime).

Dica

Durante uma atualização, o Mecanismo de Banco de Dados usa os comentários ofuscados armazenados em sys.sql_modules para recriar procedimentos.
EXECUTE AS
Especifica o contexto de segurança sob o qual o procedimento armazenado é executado depois de ser acessado.

Para obter mais informações, consulte Cláusula EXECUTE AS (Transact-SQL).
FOR REPLICATION

Aplica-se a: SQL Server 2008 a SQL Server 2014.

Especifica que procedimentos armazenados que são criados para replicação não podem ser executados no Assinante. Um procedimento armazenado criado com a opção FOR REPLICATION é usado como um filtro de procedimento armazenado e é executado somente durante a replicação. Os parâmetros não poderão ser declarados se FOR REPLICATION for especificado. Esta opção não é válida para procedimentos CLR. A opção RECOMPILE é ignorada para procedimentos criados com FOR REPLICATION.

Dica

Essa opção não está disponível em um banco de dados independente.
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Uma ou mais instruções Transact-SQL que abrangem o corpo do procedimento. Você pode usar as palavras-chave BEGIN e END para delimitar as instruções. Para obter mais informações, consulte as seções Práticas recomendadas, Comentários gerais e Limitações e restrições em CREATE PROCEDURE (Transact-SQL).
EXTERNAL NAME assembly_name**.class_name.**method_name

Aplica-se a: SQL Server 2008 a SQL Server 2014.

Especifica o método de um assembly .NET Framework para um procedimento armazenado CLR a ser referenciado. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly. Se a classe tiver um nome qualificado de namespace que use um ponto (.) para separar partes do namespace, o nome da classe deverá ser delimitado usando colchetes ([ ]) ou aspas (" "). O método especificado deve ser um método estático da classe.

Por padrão, o SQL Server não pode executar código CLR. Você pode criar, modificar e remover objetos de bancos de dados que façam referência a módulos CLR (Common Language Runtime); entretanto, não pode executar essas referências no SQL Server até habilitar a opção clr enabled. Para habilitar a opção, use sp_configure.

Dica

Não há suporte para procedimentos CLR em um banco de dados independente.

Comentários gerais

Os procedimentos armazenados Transact-SQL não podem ser modificados para serem procedimentos armazenados CLR e vice-versa.

ALTER PROCEDURE não altera permissões e não afeta nenhum procedimento armazenado dependente ou gatilhos. Entretanto, as configurações da sessão atual para QUOTED_IDENTIFIER e ANSI_NULLS são incluídas no procedimento armazenado quando ele é modificado. Se as configurações forem diferentes das que estavam em vigor quando o procedimento armazenado foi originalmente criado, o comportamento do procedimento armazenado poderá mudar.

Se a definição de procedimento anterior foi criada com WITH ENCRYPTION ou WITH RECOMPILE, essas opções estarão habilitadas somente se tiverem sido incluídas em ALTER PROCEDURE.

Para obter mais informações sobre procedimentos armazenados, consulte CREATE PROCEDURE (Transact-SQL).

Segurança

Permissões

Requer a permissão ALTER no procedimento, ou exige a associação na função de banco de dados fixa db_ddladmin.

Exemplos

O exemplo a seguir cria o procedimento armazenado uspVendorAllInfo. Esse procedimento retorna os nomes de todos os fornecedores que oferecem Ciclos da Adventure Works, os produtos que eles fornecem, suas classificações de crédito e sua disponibilidade. Depois de ser criado, este procedimento é modificado para retornar um conjunto de resultados diferente.

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

O exemplo a seguir altera o procedimento armazenado uspVendorAllInfo. Ele remove a cláusula EXECUTE AS CALLER e modifica o corpo do procedimento para retornar apenas os fornecedores que oferecem o produto especificado. As funções LEFT e CASE personalizam a aparência do conjunto de resultados.

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

Aqui está o conjunto de resultados.

Vendor Product name Rating Availability

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

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)