ALTER PROCEDURE (Transact-SQL)

Artikel
03/11/2014

Ändert eine zuvor durch Ausführen der CREATE PROCEDURE-Anweisung erstellte Prozedur in SQL Server.

Gilt für: SQL Server (SQL Server 2008 bis aktuelle Version), Windows Azure SQL-Datenbank (Ursprüngliche Version bis aktuelle Version).

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen (Transact-SQL)

Syntax

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

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. Diese Option ist für CLR-Prozeduren nicht gültig.
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 Database Engine (Datenbankmodul) den Plan für diese Prozedur nicht zwischenspeichert und die Prozedur zur Laufzeit neu kompiliert wird.
ENCRYPTION

Gilt für: SQL Server 2008 bis SQL Server 2014.

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 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 eines Upgrades 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

Gilt für: SQL Server 2008 bis SQL Server 2014.

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 ist. Diese Option ist für CLR-Prozeduren nicht gültig. Die Option RECOMPILE wird bei Prozeduren ignoriert, die mit FOR REPLICATION erstellt wurden.

Hinweis

Diese Option ist in einer enthaltenen Datenbank nicht verfügbar.
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Eine oder mehrere Transact-SQL-Anweisungen, die den Textkörper der Prozedur umfassen. Sie können die optionalen BEGIN- und END-Schlüsselwörter zum Einschließen der Anweisungen verwenden. Weitere Informationen hierzu erhalten Sie in den folgenden Abschnitten zu bewährten Methoden, allgemeinen Hinweisen und Einschränkungen in CREATE PROCEDURE (Transact-SQL).
EXTERNAL NAME assembly_name**.class_name.**method_name

Gilt für: SQL Server 2008 bis SQL Server 2014.

Gibt für eine gespeicherte CLR-Prozedur, auf die verwiesen werden soll, die Methode einer .NET Framework-Assembly an. 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.

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.

Hinweis

CLR-Prozeduren werden in einer enthaltenen Datenbank nicht unterstützt.

Allgemeine Hinweise

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

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.

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.

Weitere Informationen zu gespeicherten Prozeduren finden Sie unter CREATE PROCEDURE (Transact-SQL).

Sicherheit

Berechtigungen

Erfordert die ALTER-Berechtigung für die Prozedur oder die Mitgliedschaft in der festen Datenbankrolle db_ddladmin.

Beispiele

Im folgenden Beispiel wird die gespeicherte uspVendorAllInfo-Prozedur 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.

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

Im folgenden Beispiel wird die gespeicherte uspVendorAllInfo-Prozedur geändert. Die EXECUTE AS CALLER-Klausel wird entfernt, und der Textkörper der Prozedur wird 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.

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

Dies ist das Resultset.

Vendor Product name Rating Availability

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

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)