CREATE PROCEDURE (Transact-SQL)

Erstellt eine gespeicherte Prozedur. Eine gespeicherte Prozedur ist eine gespeicherte Auflistung von Transact-SQL-Anweisungen oder ein Verweis auf eine CLR-Methode (Common Language Runtime) von Microsoft .NET Framework, die vom Benutzer angegebene Parameter entgegennehmen und zurückgeben kann. Prozeduren können für die permanente oder temporäre Verwendung in einer Sitzung (lokale temporäre Prozeduren) oder für die temporäre Verwendung in allen Sitzungen (globale temporäre Prozeduren) erstellt werden.

Gespeicherte Prozeduren können auch so erstellt werden, dass sie beim Start einer Instanz von SQL Server automatisch ausgeführt werden.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

CREATE { 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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
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 neuen gespeicherten Prozedur. Prozedurnamen müssen den Regeln für Bezeichner entsprechen und innerhalb des Schemas eindeutig sein.

    Es wird nachdrücklich empfohlen, das Präfix sp_ nicht im Prozedurnamen zu verwenden. Dieses Präfix wird von SQL Server verwendet, um gespeicherte Systemprozeduren zu bestimmen. Weitere Informationen finden Sie unter Erstellen von gespeicherten Prozeduren (Datenbankmodul).

    Lokale oder globale temporäre Prozeduren können erstellt werden, indem procedure_name ein einzelnes Nummernzeichen (#) (#procedure_name) für lokale temporäre Prozeduren und ein doppeltes Nummernzeichen für globale temporäre Prozeduren (##procedure_name) vorangestellt wird. Temporäre Namen können nicht für CLR-gespeicherte Prozeduren angegeben werden.

    Der vollständige Name einer gespeicherten Prozedur oder einer globalen temporären gespeicherten Prozedur, einschließlich ##, darf 128 Zeichen nicht überschreiten. Der vollständige Name einer lokalen temporären gespeicherten Prozedur, einschließlich #, darf 116 Zeichen nicht überschreiten.

  • **;**number
    Eine optionale ganze Zahl zum Gruppieren von Prozeduren mit dem gleichen Namen. Diese gruppierten Prozeduren können alle mit einer DROP PROCEDURE-Anweisung gelöscht werden. So können beispielsweise die in einer Anwendung namens orders verwendeten Prozeduren mit orderproc;1, orderproc;2 usw. benannt sein. Die DROP PROCEDURE-Anweisung orderproc löscht dann die gesamte Gruppe. Wenn der Name begrenzte Bezeichner enthält, sollte die Nummer nicht als Teil des Bezeichners eingeschlossen sein. Verwenden Sie die entsprechenden Trennzeichen nur um procedure_name herum.

    Für nummerierte gespeicherte Prozeduren gelten die folgenden Einschränkungen:

    • Sie dürfen weder xml- noch CLR-benutzerdefinierte Typen als Datentypen verwenden.

    • Sie dürfen keine Planhinweisliste für eine nummerierte gespeicherte Prozedur erstellen.

    HinweisHinweis

    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. Sie können einen oder mehrere Parameter in einer CREATE PROCEDURE-Anweisung deklarieren. Der Benutzer muss beim Aufrufen der Prozedur den Wert jedes deklarierten Parameters bereitstellen, sofern kein Standardwert für den Parameter definiert oder der Wert nicht auf den eines anderen Parameters festgelegt ist. Eine gespeicherte Prozedur kann maximal 2.100 Parameter haben. Wenn eine Prozedur Tabellenwertparameter enthält und der Parameter im Aufruf fehlt, wird standardmäßig eine leere Tabelle übergeben.

    Geben Sie einen Parameternamen an, der mit dem at-Zeichen (@) beginnt. Der Parametername muss den Regeln für Bezeichner entsprechen. Parameter gelten lokal in der jeweiligen Prozedur, d. h., dass Sie die gleichen Parameternamen in anderen Prozeduren verwenden können. Parameter können standardmäßig nur die Stelle von Konstantenausdrücken einnehmen. Sie können nicht anstelle von Tabellennamen, Spaltennamen oder Namen anderer Datenbankobjekte verwendet werden. Weitere Informationen finden Sie unter EXECUTE (Transact-SQL).

    Parameter können nicht deklariert werden, wenn FOR REPLICATION angegeben ist.

  • [ type_schema_name**.** ] data_type
    Der Datentyp des Parameters und das Schema, zu dem der Parameter gehört. Alle Datentypen können als Parameter für eine gespeicherte Prozedur von Transact-SQL verwendet werden. Sie können einen benutzerdefinierten Tabellentyp verwenden, um einen Tabellenwertparameter als Parameter für gespeicherte Prozeduren von Transact-SQL zu deklarieren. Tabellenwertparameter können nur als Eingabeparameter festgelegt werden, und sie müssen von einem READONLY-Schlüsselwort begleitet werden. Der cursor-Datentyp kann nur für OUTPUT-Parameter verwendet werden. Bei Angabe des cursor-Datentyps müssen die Schlüsselwörter VARYING und OUTPUT ebenfalls angegeben werden. Die Anzahl der zulässigen Ausgabeparameter vom cursor-Datentyp ist nicht begrenzt.

    Bei CLR-gespeicherten Prozeduren können char, varchar, text, ntext, image, cursor, benutzerdefinierte Tabellentypen und table nicht als Parameter angegeben werden. Weitere Informationen zu Entsprechungen zwischen CLR-Typen und zu SQL Server-Systemdatentypen finden Sie unter Zuordnen von CLR-Parameterdaten. Weitere Informationen zu SQL Server-Systemdatentabellen und ihrer Syntax finden Sie unter Datentypen (Transact-SQL).

    Wenn es sich beim Datentyp des Parameters um einen CLR-benutzerdefinierten Typ handelt, müssen Sie über die EXECUTE-Berechtigung für diesen Typ verfügen.

    Wenn type_schema_name nicht angegeben ist, verweist SQL Server Database Engine (Datenbankmodul) auf type_name in der folgenden Reihenfolge:

    • Die SQL Server-Systemdatentabellen

    • Das Standardschema des aktuellen Benutzers in der aktuellen Datenbank

    • Das dbo-Schema in der aktuellen Datenbank

    Bei nummerierten gespeicherten Prozeduren darf der Datentyp nicht xml bzw. kein CLR-benutzerdefinierter Typ sein.

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

  • default
    Ein Standardwert für den Parameter. Wenn ein default-Wert definiert ist, kann die Prozedur ausgeführt werden, ohne dass ein Wert für den entsprechenden Parameter angegeben wird. Der Standardwert muss eine Konstante oder NULL sein. Wenn die Prozedur den Parameter mit dem LIKE-Schlüsselwort verwendet, kann er die folgenden Platzhalterzeichen enthalten: % _ [] und [^].

    HinweisHinweis

    Standardwerte werden in der sys.parameters.default-Spalte nur für CLR-Prozeduren erfasst. Diese Spalte hat für Transact-SQL-Prozedurparameter den Wert NULL.

  • OUTPUT
    Gibt an, dass es sich bei dem Parameter um einen Ausgabeparameter handelt. Der Wert dieser Option kann an die aufrufende EXECUTE-Anweisung zurückgegeben werden. Verwenden Sie OUTPUT-Parameter, um Informationen an die aufrufende Prozedur zurückzugeben. text-, ntext- und image-Parameter können als OUTPUT-Parameter verwendet werden, es sei denn, es handelt sich bei der Prozedur um eine CLR-Prozedur. Ein Ausgabeparameter, der das OUTPUT-Schlüsselwort verwendet, kann ein Cursorplatzhalter sein, es sei denn, es handelt sich bei der Prozedur um eine CLR-Prozedur. Ein benutzerdefinierter Tabellentyp kann nicht als OUTPUT-Parameter einer gespeicherten Prozedur angegeben werden.

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

  • RECOMPILE
    Gibt an, dass Database Engine (Datenbankmodul) den Plan für diese Prozedur nicht zwischenspeichert. Die Prozedur wird zur Laufzeit kompiliert. Sie können diese Option nicht verwenden, wenn FOR REPLICATION angegeben ist. RECOMPILE kann nicht für CLR-gespeicherte Prozeduren angegeben werden.

    Verwenden Sie den RECOMPILE-Abfragehinweis, damit Database Engine (Datenbankmodul) Pläne für einzelne Abfragen innerhalb einer gespeicherten Prozedur verwirft. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL). Verwenden Sie den RECOMPILE-Abfragehinweis, wenn atypische oder temporäre Werte nur in einer Untergruppe von Abfragen verwendet werden, die zu der gespeicherten Prozedur gehören.

  • ENCRYPTION
    Gibt an, dass SQL Server den Originaltext der CREATE 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 entschlüsselte Prozedur zur Laufzeit vom Arbeitsspeicher abrufen. Weitere Informationen zum Zugreifen auf Systemmetadaten finden Sie unter Konfigurieren der Sichtbarkeit von Metadaten.

    Diese Option ist für CLR-gespeicherte Prozeduren nicht gültig.

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

  • EXECUTE AS
    Gibt den Sicherheitskontext an, unter dem die gespeicherte Prozedur ausgeführt wird.

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

  • FOR REPLICATION
    Gibt an, dass für die Replikation erstellten gespeicherten 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. FOR REPLICATION kann nicht für CLR-gespeicherte Prozeduren angegeben werden. Die Option RECOMPILE wird bei Prozeduren ignoriert, die mit FOR REPLICATION erstellt wurden.

    Eine FOR REPLICATION-Prozedur hat einen RF-Objekttyp in sys.objects und in sys.procedures.

  • <sql_statement>
    Eine oder mehrere Transact-SQL-Anweisungen, die in die Prozedur eingeschlossen werden. Weitere Informationen zu einigen geltenden Begrenzungen finden Sie im Abschnitt mit den Hinweisen.

  • EXTERNAL NAME assembly_name**.class_name.method_name
    Gibt die Methode einer .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 einen mit einem Namespace qualifizierten Namen hat, in dem ein Punkt (
    .) verwendet wird, um die einzelnen Bestandteile des Namespace voneinander zu trennen, muss der Klassenname durch eckige Klammern ([]) oder Anführungszeichen (""**) begrenzt werden. Bei der angegebenen Methode muss es sich um eine statische Methode der Klasse handeln.

    HinweisHinweis

    Standardmäßig kann SQL Server keinen CLR-Code ausführen. Sie können Datenbankobjekte, die auf CLR-Module verweisen, erstellen, ändern und löschen. Sie können jedoch diese Verweise in SQL Server erst dann ausführen, wenn Sie die Option clr enabled aktiviert haben. Verwenden Sie sp_configure, um die Option zu aktivieren.

Hinweise

Für eine gespeicherte Prozedur gilt keine vordefinierte maximale Größe.

Eine benutzerdefinierte gespeicherte Prozedur kann nur in der aktuellen Datenbank erstellt werden. Temporäre Prozeduren stellen eine Ausnahme dar, da sie immer in tempdb erstellt werden. Wenn ein Schemaname nicht angegeben ist, wird das Standardschema des Benutzers verwendet, der die Prozedur erstellt. Weitere Informationen zu Schemas finden Sie unter Trennung von Benutzer und Schema.

Die CREATE PROCEDURE-Anweisung kann nicht mit anderen Transact-SQL-Anweisungen in einem einzelnen Batch kombiniert werden.

Für Parameter sind standardmäßig NULL-Werte zugelassen. Wird als Parameter ein NULL-Wert übergeben, und wird dieser Parameter in einer CREATE TABLE- oder ALTER TABLE-Anweisung für eine Spalte verwendet, die keine NULL-Werte zulässt, erzeugt Database Engine (Datenbankmodul) einen Fehler. Um zu verhindern, dass ein NULL-Wert an eine Spalte übergeben wird, die keine NULL-Werte zulässt, fügen Sie der Prozedur entweder Programmierlogik hinzu, oder verwenden Sie einen Standardwert für die Spalte mithilfe des DEFAULT-Schlüsselworts der CREATE TABLE- oder ALTER TABLE-Anweisung.

Es wird empfohlen, NULL- oder NOT NULL-Werte explizit für jede Spalte in einer temporären Tabelle anzugeben. Die Optionen ANSI_DFLT_ON und ANSI_DFLT_OFF steuern, wie Database Engine (Datenbankmodul) den Spalten die Attribute NULL oder NOT NULL zuweist, wenn diese Attribute nicht in einer CREATE TABLE- oder ALTER TABLE-Anweisung angegeben sind. Wenn eine Verbindung eine gespeicherte Prozedur ausführt und für diese Optionen andere Einstellungen verwendet als die Verbindung, die die Prozedur erstellt hat, weisen die Spalten der für die zweite Verbindung erstellten Tabelle möglicherweise eine andere NULL-Zulässigkeit und ein anderes Verhalten auf. Wenn NULL oder NOT NULL explizit für jede Spalte angegeben ist, werden die temporären Tabellen für alle Verbindungen, die die gespeicherte Prozedur ausführen, mit derselben NULL-Zulässigkeit erstellt.

Verwenden von SET-Optionen

Database Engine (Datenbankmodul) speichert die Einstellungen sowohl für SET QUOTED_IDENTIFIER als auch für SET ANSI_NULLS, wenn eine gespeicherte Prozedur von Transact-SQL erstellt oder geändert wird. Diese Originaleinstellungen werden verwendet, wenn die gespeicherte Prozedur ausgeführt wird. Deshalb werden alle Clientsitzungseinstellungen für SET QUOTED_IDENTIFIER und SET ANSI_NULLS während der Ausführung der gespeicherten Prozedur ignoriert. Andere SET-Optionen, wie z. B. SET ARITHABORT, SET ANSI_WARNINGS oder SET ANSI_PADDINGS, werden nicht gespeichert, wenn eine gespeicherte Prozedur erstellt oder geändert wird. Wenn die Logik der gespeicherten Prozedur von einer bestimmten Einstellung abhängig ist, schließen Sie eine SET-Anweisung am Anfang der Prozedur ein, um die richtige Einstellung sicherzustellen. Wenn eine SET-Anweisung aus einer gespeicherten Prozedur heraus ausgeführt wird, bleibt die betreffende Einstellung nur so lange in Kraft, bis die Ausführung der gespeicherten Prozedur abgeschlossen ist. Die Einstellung wird dann mit dem Wert wiederhergestellt, den sie hatte, als die gespeicherte Prozedur aufgerufen wurde. Dies gibt einzelnen Clients die Möglichkeit, die gewünschten Optionen festzulegen, ohne die Logik der gespeicherten Prozedur zu beeinflussen.

HinweisHinweis

ANSI_WARNINGS wird beim Übergeben von Parametern in einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batchanweisung nicht berücksichtigt. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die INSERT- oder UPDATE-Anweisung wird erfolgreich ausgeführt.

Verwenden von Parametern mit CLR-gespeicherten Prozeduren

Die Parameter einer CLR-gespeicherten Prozedur können beliebige skalare SQL Server-Systemdatentypen sein.

Die in <method_specifier> angegebene Methode muss die folgenden Merkmale aufweisen, damit Database Engine (Datenbankmodul) auf die richtige Methode verweisen kann, wenn sie in .NET Framework überladen wird.

  • Sie muss als statische Methode deklariert sein.

  • Sie muss dieselbe Anzahl von Parametern erhalten wie die der in der Prozedur enthaltenen Parameter.

  • Sie darf kein Konstruktor oder Destruktor ihrer Klasse sein.

  • Sie muss Parametertypen verwenden, die mit den Datentypen der jeweiligen Parameter der SQL Server-Prozedur kompatibel sind. Weitere Informationen zur Übereinstimmung von SQL Server-Datentypen mit .NET Framework-Datentypen finden Sie unter Zuordnen von CLR-Parameterdaten.

  • Sie muss VOID zurückgeben oder einen Wert des Typs SQLInt32, SQLInt16, System.Int32 oder des Typs System.Int16.

  • Sie muss ihre Parameter durch Verweis, nicht als Wert zurückgeben, wenn OUTPUT für eine beliebige Parameterdeklaration angegeben ist.

Abrufen von Informationen über gespeicherte Prozeduren

Verwenden Sie die sys.sql_modules-Katalogsicht in der Datenbank, in der die Prozedur vorhanden ist, um die Definition einer gespeicherten Prozedur von Transact-SQL anzuzeigen.

Beispiel:

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';
HinweisHinweis

Der Text einer mit der ENCRYPTION-Option erstellten gespeicherten Prozedur kann nicht mit der sys.sql_modules-Katalogsicht angezeigt werden.

Führen Sie für einen Bericht über die Objekte, auf die mit einer Prozedur verwiesen wird, eine Abfrage der sys.sql_expression_dependencies-Katalogsicht aus, oder verwenden Sie sys.dm_sql_referenced_entities und sys.dm_sql_referencing_entities.

Verwenden Sie zum Anzeigen von Informationen über CLR-gespeicherte Prozeduren die sys.assembly_modules-Katalogsicht in der Datenbank, in der die Prozedur vorhanden ist.

Verwenden Sie zum Anzeigen von Informationen über die in einer gespeicherten Prozedur definierten Parameter die sys.parameters-Katalogsicht in der Datenbank, in der die Prozedur vorhanden ist.

Verzögerte Namensauflösung

Sie können gespeicherte Prozeduren erstellen, die auf noch nicht vorhandene Tabellen verweisen. Zum Zeitpunkt der Erstellung wird nur die Syntaxüberprüfung ausgeführt. Die gespeicherte Prozedur wird erst dann kompiliert, wenn sie zum ersten Mal ausgeführt wird. Erst während des Kompilierens werden alle Objekte aufgelöst, auf die in der gespeicherten Prozedur verwiesen wird. Daher kann eine syntaktisch richtige gespeicherte Prozedur, die auf noch nicht vorhandene Tabellen verweist, erfolgreich erstellt werden. Die Prozedur schlägt jedoch zur Laufzeit fehl, wenn die Tabellen, auf die verwiesen wird, nicht vorhanden sind. Weitere Informationen finden Sie unter Verzögerte Namensauflösung und Kompilierung.

Ausführen von gespeicherten Prozeduren

Es wird nachdrücklich empfohlen, den Namen der gespeicherten Prozedur mit einem Schemanamen zu kennzeichnen, wenn Sie eine benutzerdefinierte gespeicherte Prozedur ausführen, entweder in einem Batch oder innerhalb eines Moduls, wie etwa eine benutzerdefinierte gespeicherte Prozedur oder Funktion.

Parameterwerte können bereitgestellt werden, wenn eine gespeicherte Prozedur so geschrieben wurde, dass sie Parameter annimmt. Beim bereitgestellten Wert kann es sich um eine Konstante oder eine Variable handeln. Sie können einen Funktionsnamen nicht als Parameterwert festlegen. Variablen können benutzerdefinierte oder Systemvariablen sein, wie z. B. @@SPID.

Weitere Informationen finden Sie unter Ausführen von gespeicherten Prozeduren (Datenbankmodul).

Wenn eine Prozedur zum ersten Mal ausgeführt wird, wird sie kompiliert, um einen optimalen Zugriffsplan für den Datenabruf zu bestimmen. Nachfolgende Ausführungen der gespeicherten Prozedur können den bereits generierten Plan erneut verwenden, wenn dieser weiterhin im Plancache von Database Engine (Datenbankmodul) vorhanden ist. Weitere Informationen finden Sie unter Zwischenspeichern und Wiederverwenden von Ausführungsplänen.

Parameter mit dem cursor-Datentyp

Gespeicherte Prozeduren von Transact-SQL können den cursor-Datentyp nur für OUTPUT-Parameter verwenden. Wenn der cursor-Datentyp für einen Parameter angegeben ist, ist sowohl der VARYING- als auch der OUTPUT-Parameter erforderlich. Wenn das VARYING-Schlüsselwort für einen Parameter angegeben ist, muss der Datentyp cursor sein, und das OUTPUT-Schlüsselwort muss angegeben werden. Weitere Informationen finden Sie unter Verwenden des cursor-Datentyps in OUTPUT-Parametern.

Temporäre gespeicherte Prozeduren

Database Engine (Datenbankmodul) unterstützt zwei Arten von temporären Prozeduren: lokale und globale temporäre Prozeduren. Eine lokale temporäre Prozedur ist nur für die Verbindung sichtbar, von der sie erstellt wurde. Eine globale temporäre Prozedur steht allen Verbindungen zur Verfügung. Lokale temporäre Prozeduren werden am Ende der aktuellen Sitzung automatisch gelöscht. Globale temporäre Prozeduren werden am Ende der letzten Sitzung gelöscht, von der die Prozedur verwendet wird. Weitere Informationen finden Sie unter Erstellen von gespeicherten Prozeduren (Datenbankmodul).

Automatisches Ausführen von gespeicherten Prozeduren

Eine oder mehrere gespeicherte Prozeduren können beim Start von SQL Server automatisch ausgeführt werden. Die gespeicherten Prozeduren müssen vom Systemadministrator in der master-Datenbank erstellt und unter der festen Serverrolle sysadmin als Hintergrundprozess ausgeführt werden. Die Prozeduren dürfen keine Eingabe- oder Ausgabeparameter besitzen. Weitere Informationen finden Sie unter Automatische Ausführung gespeicherter Prozeduren.

Schachteln gespeicherter Prozeduren

Gespeicherte Prozeduren können geschachtelt sein. Das heißt, dass eine gespeicherte Prozedur eine andere aufrufen kann. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Prozedur mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Prozedur die Ausführung beendet. Gespeicherte Prozeduren können bis auf 32 Ebenen geschachtelt werden. Weitere Informationen finden Sie unter Schachteln gespeicherter Prozeduren.

Verwenden Sie die folgenden Leistungsindikatoren, um die Größe einer kompilierten gespeicherten Prozedur zu schätzen.

Name des Systemmonitorobjekts

Name des Leistungsindikators

SQLServer:Plancache

Cachetrefferquote

 

Cacheseiten

 

Cacheobjektzähler*

* Diese Indikatoren sind für verschiedene Kategorien von Cacheobjekten verfügbar, einschließlich Ad-hoc-SQL-Anweisungen, vorbereiteten SQL-Anweisungen, Prozeduren, Triggern usw.

Weitere Informationen finden Sie unter SQL Server, Plancache-Objekt.

<sql_statement>-Beschränkungen

Alle SET-Anweisungen können in einer gespeicherten Prozedur angegeben werden, mit Ausnahme von SET SHOWPLAN_TEXT und SET SHOWPLAN_ALL. Sie müssen die einzigen Anweisungen im Batch sein. Die ausgewählte SET-Option bleibt während der Ausführung der gespeicherten Prozedur in Kraft und wird dann auf die vorherige Einstellung zurückgesetzt.

Wenn außer dem Besitzer der gespeicherten Prozedur auch andere Benutzer die gespeicherte Prozedur verwenden können sollen, müssen die Objektnamen innerhalb einer gespeicherten Prozedur, die in allen DLL-Anweisungen (Data Definition Language) verwendet werden (z. B. CREATE-, ALTER- oder DROP-Anweisungen, DBCC-Anweisungen, EXECUTE- und dynamische SQL-Anweisungen), mit dem Namen des Objektschemas gekennzeichnet werden. Weitere Informationen finden Sie unter Entwerfen gespeicherter Prozeduren (Datenbankmodul).

Berechtigungen

Erfordert die CREATE PROCEDURE-Berechtigung in der Datenbank und die ALTER-Berechtigung auf dem Schema, in dem die Prozedur erstellt wird.

Bei CLR-gespeicherten Prozeduren müssen Sie der Besitzer der Assembly sein, auf die in <method_specifier> verwiesen wird, oder über die REFERENCES-Berechtigung für diese Assembly verfügen.

Beispiele

A. Verwenden einer einfachen Prozedur

Die folgende gespeicherte Prozedur gibt alle Mitarbeiter (mit Vor- und Nachnamen), ihre Titel und ihre Abteilungsnamen aus einer Sicht zurück. Diese gespeicherte Prozedur verwendet keine Parameter.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

Die gespeicherte Prozedur uspGetEmployees kann auf folgende Arten ausgeführt werden:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Verwenden einer einfachen Prozedur mit Parametern

Die folgende gespeicherte Prozedur gibt nur den angegebenen Mitarbeiter (mit Vor- und Nachnamen), seinen Titel und Abteilungsnamen aus einer Sicht zurück. Diese gespeicherte Prozedur nimmt genaue Übereinstimmungen für die übergebenen Parameter an.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

Die gespeicherte Prozedur uspGetEmployees kann auf folgende Arten ausgeführt werden:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. Verwenden einer einfachen Prozedur mit Platzhalterparametern

Die folgende gespeicherte Prozedur gibt nur die angegebenen Mitarbeiter (mit Vor- und Nachnamen), ihre Titel und Abteilungsnamen aus einer Sicht zurück. Diese gespeicherte Prozedur führt mit den übergebenen Parametern einen Mustervergleich aus oder verwendet die voreingestellten Standardwerte (Nachnamen, die mit dem Buchstaben D beginnen), wenn keine Parameter bereitgestellt sind.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

Die gespeicherte Prozedur uspGetEmployees2 kann in verschiedenen Kombinationen ausgeführt werden. Hier werden nur einige Kombinationen gezeigt:

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. Zurückgeben von mehreren Resultsets

Die folgende gespeicherte Prozedur gibt zwei Resultsets zurück.

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. Verwenden von OUTPUT-Parametern

Im folgenden Beispiel wird die gespeicherte Prozedur uspGetList erstellt. Diese Prozedur gibt eine Liste der Produkte zurück, deren Preise einen angegebenen Betrag nicht überschreiten. Das Beispiel zeigt die Verwendung von mehreren SELECT- und mehreren OUTPUT-Parametern. OUTPUT-Parameter ermöglichen einer externen Prozedur, einem Batch oder mehreren Transact-SQL-Anweisungen den Zugriff auf einen Satz von Werten während der Ausführung der Prozedur.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Führen Sie uspGetList aus, um eine Liste der Adventure Works-Produkte (Bikes) zurückzugeben, die weniger als $700 kosten. Die OUTPUT-Parameter @Cost und @ComparePrices werden mit einer Ablaufsteuerungssprache verwendet, um eine Meldung an das Fenster Meldungen zurückzugeben.

HinweisHinweis

Die OUTPUT-Variable muss definiert sein, wenn die Prozedur erstellt wird, und auch dann, wenn die Variable verwendet wird. Der Parametername und der Variablenname müssen nicht übereinstimmen; jedoch müssen der Datentyp und die Position des Parameters übereinstimmen, es sei denn, es wird @ListPrice= variable verwendet.

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Im Folgenden wird ein Teil des Resultsets aufgeführt:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

F. Verwenden der WITH RECOMPILE-Option

Die WITH RECOMPILE-Klausel ist hilfreich, wenn die für die Prozedur bereitgestellten Parameter nicht typisch sind und wenn ein neuer Ausführungsplan nicht zwischengespeichert oder im Arbeitsspeicher abgelegt werden soll.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. Verwenden der WITH ENCRYPTION-Option

Im folgenden Beispiel wird die gespeicherte Prozedur HumanResources.uspEncryptThis erstellt.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

Die WITH ENCRYPTION-Option verhindert, dass die Definition der gespeicherten Prozedur zurückgegeben wird, wie in den folgenden Beispielen gezeigt wird.

Ausführen von sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Dies ist das Resultset.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Direkte Abfrage der sys.sql_modules-Katalogsicht:

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Dies ist das Resultset.

definition
----------------------
NULL

(1 row(s) affected)

H. Verwenden der verzögerten Namensauflösung

Im folgenden Beispiel wird die Prozedur uspProc1 erstellt. Sie verwendet die verzögerte Namensauflösung. Die gespeicherte Prozedur wird erstellt, obwohl die Tabelle, auf die verwiesen wird, zum Zeitpunkt des Kompilierens nicht vorhanden ist. Wenn die Prozedur ausgeführt wird, muss die Tabelle allerdings vorhanden sein.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

Führen Sie die folgende Abfrage aus, um zu überprüfen, ob die gespeicherte Prozedur erstellt wurde:

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

Dies ist das Resultset.

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. Verwenden der EXECUTE AS-Klausel

Das folgende Beispiel zeigt die Verwendung der EXECUTE AS-Klausel, um den Sicherheitskontext anzugeben, in dem eine gespeicherte Prozedur ausgeführt werden kann. Im Beispiel legt die Option CALLER fest, dass die Prozedur im Kontext des Benutzers, der die Prozedur aufruft, ausgeführt werden kann.

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

J. Erstellen einer CLR-gespeicherten Prozedur

Im folgenden Beispiel wird die gespeicherte Prozedur GetPhotoFromDB erstellt, die auf die GetPhotoFromDB-Methode der LargeObjectBinary-Klasse in der HandlingLOBUsingCLR -Assembly verweist. Bevor die gespeicherte Prozedur erstellt wird, wird die HandlingLOBUsingCLR-Assembly in der lokalen Datenbank registriert.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

K. Verwenden eines OUTPUT-Cursorparameters

OUTPUT-Cursorparameter werden verwendet, um einen Cursor aus einer gespeicherten Prozedur an den aufrufenden Batch, die aufrufende gespeicherte Prozedur oder den aufrufenden Trigger zurückzugeben.

Zuerst wird die Prozedur erstellt, die einen Cursor für die Currency-Tabelle deklariert und dann öffnet:

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

Als Nächstes wird ein Batch ausgeführt, der eine lokale cursor-Variable deklariert, die Prozedur ausführt, um der lokalen Variablen den Cursor zuzuordnen, und dann die Zeilen aus dem Cursor abruft.

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Siehe auch

Aufgaben

Verweis

Konzepte

Andere Ressourcen