ALTER PROCEDURE (Transact-SQL)

Article
05/23/2023

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Modifie une procédure déjà créée en exécutant l'instruction CREATE PROCEDURE dans SQL Server.

Conventions de la syntaxe Transact-SQL (Transact-SQL)

Syntaxe

-- Syntax for SQL Server and Azure SQL Database
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
    [ { @parameter_name [ 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 ]

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

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameterdata_type } [= ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [ ; ] [ ,...n ] [ END ] }  
[;]

Arguments

schema_name
Nom du schéma auquel appartient la procédure.

procedure_name
Nom de la procédure à modifier. Les noms des procédures doivent respecter les conventions concernant les identificateurs.

; number
Entier facultatif existant utilisé pour regrouper les procédures de même nom de façon à pouvoir les supprimer au moyen d’une instruction DROP PROCEDURE.

Remarque

Cette fonctionnalité sera supprimée dans une version future de SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

@parameter_name
Paramètre de la procédure. Il est possible de spécifier jusqu'à 2 100 paramètres.

[ type_schema_name. ] data_type
Type de données du paramètre et du schéma auquel elle appartient.

Pour plus d’informations sur les restrictions applicables aux types de données, consultez CREATE PROCEDURE (Transact-SQL).

VARYING
Spécifie le jeu de résultats pris en charge comme paramètre de sortie. La procédure stockée construit dynamiquement ce paramètre. Son contenu est variable. S'applique seulement aux paramètres de type cursor. Cette option n'est pas valide pour les procédures CLR.

default
Valeur par défaut pour le paramètre.

OUT | OUTPUT
Indique que le paramètre est un paramètre renvoyé.

READONLY
Indique que le paramètre ne peut pas être mis à jour ou modifié dans le corps de la procédure. Si le type de paramètre est un type de table, READONLY doit être spécifié.

RECOMPILE
Indique que le Moteur de base de données n'utilise pas le cache pour le plan de cette procédure et que la procédure est recompilée à l'exécution.

ENCRYPTION
S’applique à : SQL Server (SQL Server 2008 (10.0.x) et versions ultérieures) et Azure SQL Database.

Indique que le Moteur de base de données se charge de convertir le texte d'origine provenant de l'instruction ALTER PROCEDURE dans un format d'obfuscation. La sortie générée par l'obfuscation n'est pas visible directement dans les affichages catalogue de SQL Server. Les utilisateurs n'ayant pas accès aux tables système ou aux fichiers de base de données ne peuvent pas récupérer le texte d'obfuscation. Le texte est cependant à la disposition des utilisateurs disposant de privilèges et qui peuvent accéder aux tables système par le biais du port DAC ou qui accèdent directement aux fichiers de bases de données. Les utilisateurs qui peuvent associer un débogueur au processus serveur peuvent également récupérer la procédure d'origine de la mémoire au moment de l'exécution. Pour plus d’informations sur l’accès aux métadonnées système, consultez Configuration de la visibilité des métadonnées.

Les procédures créées à l'aide de cette option ne peuvent pas être publiées dans le cadre d'une réplication SQL Server.

Cette option ne peut pas être spécifiée pour les procédures stockées CLR (Common Language Runtime).

Notes

Au cours d’une mise à niveau, le Moteur de base de données utilise les commentaires d’obfuscation stockés dans sys.sql_modules pour recréer des procédures.

EXECUTE AS
Spécifie le contexte de sécurité dans lequel exécuter la procédure stockée après y avoir accédé.

Pour plus d’informations, consultez Clause EXECUTE AS (Transact-SQL).

FOR REPLICATION

Indique qu'il n'est pas possible d'exécuter sur l'Abonné les procédures stockées créées pour la réplication. Une procédure stockée créée avec l'option FOR REPLICATION est utilisée comme filtre de procédure stockée et n'est exécutée que lors de la réplication. Il n'est pas possible de déclarer des paramètres si FOR REPLICATION est spécifié. Cette option n'est pas valide pour les procédures CLR. L'option RECOMPILE est ignorée pour les procédures créées avec l'option FOR REPLICATION.

Notes

Cette option n'est pas disponible dans une base de données autonome.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Une ou plusieurs instructions Transact-SQL comprenant le corps de la procédure. Vous pouvez utiliser les mots clés facultatifs BEGIN et END pour délimiter les instructions. Pour plus d’informations, consultez les sections Bonnes pratiques, Remarques d’ordre général et Limitations et restrictions dans CREATE PROCEDURE (Transact-SQL).

EXTERNAL NAME assembly_name.class_name.method_name
S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.

Précise la méthode d’un assembly .NET Framework pour créer une référence à une procédure stockée CLR. class_name doit être un identificateur SQL Server valide et doit exister en tant que classe dans l’assembly. Si la classe a un nom qualifié par un espace de noms, utilisez un point (.) pour séparer les parties de l’espace de noms. Le nom de la classe doit figurer entre crochets ([]) ou entre guillemets doubles (""). La méthode spécifiée doit être une méthode statique de la classe.

Par défaut, SQL Server ne peut pas exécuter du code CLR. Vous pouvez créer, modifier et supprimer des objets d’une base de données qui font référence à des modules CLR (Common Language Runtime) ; cependant, vous ne pouvez pas exécuter ces références dans SQL Server tant que vous n’avez pas activé l’option CLR enabled. Pour activer cette option, utilisez sp_configure.

Notes

Les procédures CLR ne sont pas prises en charge dans une base de données autonome.

Remarques d'ordre général

Les procédures stockées Transact-SQL ne peuvent pas être transformées en procédures stockées CLR, et inversement.

ALTER PROCEDURE ne modifie pas les autorisations et n'affecte aucune procédure stockée ni aucun déclencheur dépendants. Cependant, les paramètres QUOTED_IDENTIFIER et ANSI_NULLS de la session active sont inclus dans la procédure stockée lorsque celle-ci est modifiée. Si les paramètres sont différents des paramètres actifs lors de la création de la procédure stockée, le comportement de celle-ci peut changer.

Si une procédure à été créée avec les options WITH ENCRYPTION ou WITH RECOMPILE, ces options sont activées seulement si elles figurent dans l'instruction ALTER PROCEDURE.

Pour plus d’informations sur les procédures stockées, consultez CREATE PROCEDURE (Transact-SQL).

Sécurité

Autorisations

Nécessite l’autorisation ALTER sur la procédure ou l’appartenance au rôle de base de données fixe db_ddladmin.

Exemples

L'exemple suivant crée la procédure stockée uspVendorAllInfo. Cette procédure retourne le nom de tous les fournisseurs de Adventure Works Cycles, les produits qu'ils vendent, leurs conditions de crédit et leur disponibilité. Lorsque cette procédure est créée, elle est ensuite modifiée pour renvoyer un jeu de résultats différent.

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

L'exemple suivant modifie la procédure stockée uspVendorAllInfo. Il supprime la clause EXECUTE AS CALLER et modifie le corps de la procédure pour qu'elle retourne uniquement les fournisseurs qui proposent le produit spécifié. Les fonctions LEFT et CASE personnalisent l'affichage du jeu de résultats.

USE AdventureWorks2022;  
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

Voici le jeu de résultats.

Vendor               Product name  Rating    Availability  
-------------------- ------------- -------   ------------  
Proseware, Inc.      LL Crankarm   Average   No  
Vision Cycles, Inc.  LL Crankarm   Superior  Yes  
(2 row(s) affected)`

Voir aussi

CREATE PROCEDURE (Transact-SQL)
DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL)
EVENTDATA (Transact-SQL)
Procédures stockées (moteur de base de données)
sys.procedures (Transact-SQL)

Partage via

ALTER PROCEDURE (Transact-SQL)

Syntaxe

Arguments

Remarques d'ordre général

Sécurité

Autorisations

Exemples

Voir aussi

Commentaires

Ressources supplémentaires