ALTER PROCEDURE (Transact-SQL)

Ändert eine zuvor durch Ausführen der CREATE PROCEDURE-Anweisung erstellte Prozedur. ALTER PROCEDURE ändert keine Berechtigungen und wirkt sich nicht auf abhängige gespeicherte Prozeduren oder Trigger aus. Die Einstellungen der aktuellen Sitzung für QUOTED_IDENTIFIER und ANSI_NULLS werden jedoch in der gespeicherten Prozedur berücksichtigt, wenn diese geändert wird. Unterscheiden sich die Einstellungen von denen, die bei der Erstellung der gespeicherten Prozedur gültig waren, ändert sich das Verhalten der gespeicherten Prozedur möglicherweise.

Transact-SQL-Syntaxkonventionen (Transact-SQL)

Syntax

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

Argumente

• schema_name
  Der Name des Schemas, zu dem die Prozedur gehört.

• procedure_name
  Der Name der zu ändernden Prozedur. Prozedurnamen müssen den Regeln für Bezeichner entsprechen.

• **;**number
  Eine vorhandene, optionale ganze Zahl, die zum Gruppieren von Prozeduren mit dem gleichen Namen verwendet wird, damit diese mit einer einzigen DROP PROCEDURE-Anweisung gelöscht werden können.

  Hinweis
  Diese Funktion wird in zukünftigen Versionen von Microsoft SQL Server nicht mehr bereitgestellt. Verwenden Sie diese Funktion beim Entwickeln neuer Anwendungen nicht, und planen Sie das Ändern von Anwendungen, in denen es zurzeit verwendet wird.

• **@**parameter
  Ein Parameter in der Prozedur. Es können bis zu 2.100 Parameter angegeben werden.

• [ type_schema_name**.** ] data_type
  Der Datentyp des Parameters und des Schemas, zu dem dieser gehört.

  Informationen zu Datentypeinschränkungen finden Sie unter CREATE PROCEDURE (Transact-SQL).

• VARYING
  Gibt das als Ausgabeparameter unterstützte Resultset an. Dieser Parameter wird durch die gespeicherte Prozedur dynamisch erstellt, und sein Inhalt kann variieren. Gilt nur für cursor-Parameter.

• default
  Ein Standardwert für den Parameter.

• OUT | OUTPUT
  Zeigt an, dass es sich bei dem Parameter um einen Rückgabeparameter handelt.

• READONLY
  Gibt an, dass der Parameter nicht aktualisiert oder innerhalb des Texts der Prozedur geändert werden kann. Wenn der Parametertyp ein Tabellenwerttyp ist, muss READONLY angegeben werden.

• RECOMPILE
  Zeigt an, dass SQL Server 2005 Database Engine (Datenbankmodul) den Plan für diese Prozedur nicht zwischenspeichert und die Prozedur zur Laufzeit neu kompiliert wird.

• ENCRYPTION
  Gibt an, dass Database Engine (Datenbankmodul) den Originaltext der ALTER PROCEDURE-Anweisung in ein verborgenes Format umwandelt. Die Ausgabe der Verbergung ist nicht direkt in den Katalogsichten in SQL Server 2005 sichtbar. Benutzer, die keinen Zugriff auf Systemtabellen oder Datenbankdateien haben, können den verborgenen Text nicht abrufen. Der Text ist jedoch für Benutzer mit umfangreichen Privilegien verfügbar, die entweder auf die Systemtabellen über den DAC-Port oder direkt auf die Datenbankdateien zugreifen. Des Weiteren können Benutzer, die einen Debugger an den Serverprozess anfügen können, die Originalprozedur zur Laufzeit vom Arbeitsspeicher abrufen. Weitere Informationen zum Zugreifen auf Systemmetadaten finden Sie unter Konfigurieren der Sichtbarkeit von Metadaten.

  Prozeduren, die mit dieser Option erstellt wurden, können nicht als Teil der SQL Server-Replikation veröffentlicht werden.

  Die Option kann nicht für CLR-gespeicherte Prozeduren (Common Language Runtime) angegeben werden.

  Hinweis
  Während einer Aktualisierung verwendet Database Engine (Datenbankmodul) die verborgenen Kommentare, die in sys.sql_modules gespeichert sind, um Prozeduren neu zu erstellen.

• EXECUTE AS
  Gibt den Sicherheitskontext an, unter dem die gespeicherte Prozedur ausgeführt wird, nachdem auf sie zugegriffen wurde.

  Weitere Informationen finden Sie unter EXECUTE AS-Klausel (Transact-SQL).

• FOR REPLICATION
  Gibt an, dass für die Replikation erstellte gespeicherte Prozeduren nicht auf dem Abonnenten ausgeführt werden können. Eine gespeicherte Prozedur, die mit der Option FOR REPLICATION erstellt wurde, wird als Filter für gespeicherte Prozeduren verwendet und nur während der Replikation ausgeführt. Parameter können nicht deklariert werden, wenn FOR REPLICATION angegeben wurde. Für Prozeduren, die mit FOR REPLICATION erstellt wurden, wird die Option RECOMPILE ignoriert.

• AS
  Die Aktionen, die die Prozedur ausführen soll.

• <sql_statement>
  Transact-SQL-Anweisungen beliebiger Anzahl und beliebigen Typs, die in die Prozedur eingeschlossen werden sollen. Es gelten allerdings einige Beschränkungen. Weitere Informationen finden Sie im Abschnitt über <sql_statement>-Beschränkungen unter CREATE PROCEDURE (Transact-SQL).

• EXTERNAL NAME assembly_name**.class_name.method_name
  Gibt die Methode einer Microsoft.NET Framework-Assembly für eine CLR-gespeicherte Prozedur an, auf die verwiesen werden soll. class_name muss ein gültiger SQL Server-Bezeichner sein und als Klasse in der Assembly vorhanden sein. Wenn die Klasse über einen mit einem Namespace qualifizierten Namen verfügt und die einzelnen Bestandteile des Namespace durch einen Punkt (.) voneinander getrennt werden, muss der Klassenname durch Klammern ([]) oder Anführungszeichen ("** ") getrennt werden. Bei der angegebenen Methode muss es sich um eine statische Methode der Klasse handeln.

  Hinweis
  Standardmäßig kann SQL Server keinen CLR-Code ausführen. Sie können Datenbankobjekte, die auf CLR-Module (Common Language Runtime) verweisen, erstellen, ändern und löschen; bevor Sie diese Verweise in SQL Server ausführen können, müssen Sie jedoch die Option clr enabled aktivieren. Verwenden Sie sp_configure, um die Option zu aktivieren.

Hinweise

Transact-SQL-gespeicherte Prozeduren können nicht in CLR-gespeicherte Prozeduren geändert werden und umgekehrt.

Weitere Informationen finden Sie im Abschnitt mit Hinweisen unter CREATE PROCEDURE (Transact-SQL).

Hinweis
Wenn eine vorherige Prozedurdefinition mit WITH ENCRYPTION oder WITH RECOMPILE erstellt wurde, sind diese Optionen nur dann aktiviert, wenn sie in der ALTER PROCEDURE-Anweisung enthalten sind.

Berechtigungen

Für die Prozedur ist die ALTER-Berechtigung erforderlich.

Beispiele

Im folgenden Beispiel wird die gespeicherte Prozedur uspVendorAllInfo erstellt. Diese Prozedur gibt die Namen, die gelieferten Produkte, die Bonität und die Verfügbarkeit aller Hersteller, die Adventure Works Cycles beliefern, zurück. Nachdem diese Prozedur erstellt wurde, wird sie so geändert, dass ein anderes Resultset zurückgegeben wird.

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

Im folgenden Beispiel wird die gespeicherte Prozedur uspVendorAllInfo (ohne die Option EXECUTE AS) so geändert, dass nur Hersteller zurückgegeben werden, die das angegebene Produkt liefern. Mit den Funktionen LEFT und CASE wird die Darstellung des Resultsets angepasst.

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

Dies ist das Resultset.

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

(2 row(s) affected)

Siehe auch

Aufgaben

Vorgehensweise: Ändern einer gespeicherten Prozedur (SQL Server Management Studio)

Verweis

CREATE PROCEDURE (Transact-SQL)

Ablaufsteuerungssprache (Transact-SQL)

Datentypen (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

EVENTDATA (Transact-SQL)

Funktionen (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)

Konzepte

Batches

Cursor (Datenbankmodul)

Verwenden von Variablen und Parametern (Datenbankmodul)

Vornehmen von Schemaänderungen in Veröffentlichungsdatenbanken

Andere Ressourcen

Gespeicherte Prozeduren (Datenbankmodul)