• Artikel

CREATE PROCEDURE (Transact-SQL)

Erstellt eine Transact-SQL oder eine gespeicherte Common Language Runtime (CLR)-Prozedur in SQL Server 2008 R2. Gespeicherte Prozeduren gleichen den Prozeduren in anderen Programmiersprachen bezüglich der folgenden Merkmale und Fähigkeiten:

  • Annehmen von Eingabeparametern und Zurückgeben mehrerer Werte in Form von Ausgabeparametern an die aufrufende Prozedur oder den aufrufenden Batch.

  • Aufnehmen von Programmierungsanweisungen, die Operationen in der Datenbank ausführen, einschließlich des Aufrufens anderer Prozeduren.

  • Zurückgeben eines Statuswertes an eine aufrufende Prozedur oder einen aufrufenden Batch, der Erfolg oder Misserfolg (sowie die Ursache für den Misserfolg) anzeigt.

Verwenden Sie diese Anweisung, um eine dauerhafte Prozedur in der aktuellen Datenbank oder eine temporäre Prozedur in der tempdb-Datenbank zu erstellen.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

--Transact-SQL Stored Procedure 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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { 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. Prozeduren sind schemagebunden. Wird bei der Erstellung der Prozedur kein Schemaname angegeben, wird automatisch das Standardschema des die Prozedur erstellenden Benutzers zugewiesen. Weitere Informationen zu Schemas finden Sie unter Trennung von Benutzer und Schema.

  • procedure_name
    Der Name der Prozedur. Prozedurnamen müssen den Regeln für Bezeichner entsprechen und innerhalb des Schemas eindeutig sein.

    Verwenden Sie beim Benennen von Prozeduren möglichst nicht das sp_-Präfix. Dieses Präfix wird von SQL Server verwendet, um Systemprozeduren zu bestimmen. Das Verwenden des Präfixes kann zur Beschädigung von Anwendungscode führen, falls eine Systemprozedur mit dem gleichen Namen vorhanden ist. Weitere Informationen finden Sie unter Entwerfen gespeicherter 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. Eine lokale temporäre Prozedur ist nur für die Verbindung sichtbar, die sie erstellt hat. Die Prozedur wird automatisch gelöscht, wenn die Verbindung geschlossen wird. Eine globale temporäre Prozedur ist für alle Verbindungen verfügbar und wird am Ende der letzten Sitzung gelöscht, die die Prozedur verwendet. Temporäre Namen können nicht für CLR-Prozeduren angegeben werden.

    Der vollständige Name einer Prozedur oder einer globalen temporären Prozedur, einschließlich ##, darf 128 Zeichen nicht überschreiten. Der vollständige Name einer lokalen temporären 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.

    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.

    Nummerierte Prozeduren können nicht den xml-Typ oder benutzerdefinierte CLR-Typen verwenden und können nicht in einer Planhinweisliste verwendet werden.

  • @parameter
    Ein in der Prozedur deklarierter Parameter. 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.

    Mindestens ein Parameter (maximale 2 100) kann deklariert werden. 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. Enthält eine Prozedur Tabellenwertparameter und ist der Parameter im Aufruf nicht vorhanden, wird eine leere Tabelle übergeben. Parameter können nur den Platz konstanter Ausdrücke einnehmen. Sie können nicht an Stelle von Tabellennamen, Spaltennamen oder den 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 Datenbanktyp gehört.

    Datentyprichtlinien für Transact-SQL-Prozeduren:

    • Alle Transact-SQL-Datentypen können als Parameter verwendet werden.

    • Verwenden Sie den benutzerdefinierten Tabellentyp, um Tabellenwertparameter zu erstellen. Tabellenwertparameter können nur INPUT-Parameter sein und müssen vom READONLY-Schlüsselwort begleitet werden. Weitere Informationen finden Sie unter Tabellenwertparameter (Datenbankmodul).

    • cursor-Datentypen können nur OUTPUT-Parameter sein und müssen vom VARYING-Schlüsselwort begleitet werden.

    Datentyprichtlinien für CLR-Prozeduren:

    • Alle nativen SQL Server-Datentypen, die über eine Entsprechung im verwalteten Code verfügen, können als Parameter verwendet 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).

    • Tabellenwertdatentypen oder cursor-Datentypen können nicht als Parameter verwendet werden.

    • 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.

  • VARYING
    Gibt das als Ausgabeparameter unterstützte Resultset an. Dieser Parameter wird dynamisch durch die Prozedur erstellt. 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 einen Parameter. Wenn ein Standardwert für einen Parameter 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. Der konstante Wert kann ein Platzhalter sein, wodurch beim Weitergeben des Parameters an die Prozedur das LIKE-Schlüsselwort verwendet werden kann. Weitere Informationen finden Sie unten im Beispiel C.

    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.

  • OUT | OUTPUT
    Gibt an, dass es sich bei dem Parameter um einen Ausgabeparameter handelt. 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 kann ein Cursorplatzhalter sein, es sei denn, es handelt sich bei der Prozedur um eine CLR-Prozedur. Ein Tabellenwert-Datentyp kann nicht als OUTPUT-Parameter einer 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 Tabellenwerttyp ist, muss READONLY angegeben werden.

  • RECOMPILE
    Gibt an, dass das Database Engine (Datenbankmodul) keinen Abfrageplan für die Prozedur zwischenspeichert, wodurch diese bei jeder Ausführung kompiliert werden muss. Weitere Informationen zu den Gründen für eine erzwungene Neukompilierung finden Sie unter Erneutes Kompilieren gespeicherter Prozeduren. Diese Option kann nicht für CLR-Prozeduren verwendet werden, oder wenn FOR REPLICATION angegeben ist.

    Verwenden Sie den RECOMPILE-Abfragehinweis in der Abfragedefinition, damit Database Engine (Datenbankmodul) Abfragepläne für einzelne Abfragen innerhalb einer Prozedur verwirft. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

  • 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-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 Prozedur ausgeführt wird.

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

  • FOR REPLICATION
    Gibt an, dass die Prozedur für die Replikation erstellt ist. Entsprechend kann sie nicht für den Abonnenten ausgeführt werden. Eine Prozedur, die mit der Option FOR REPLICATION erstellt wurde, wird als Filter für 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-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.

  • { [ 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. Informationen hierzu erhalten Sie in den folgenden Abschnitten zu bewährten Methoden, allgemeinen Hinweisen und Einschränkungen.

  • 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 (    .) zur Trennung der Bestandteile des Namespace verwendet wird, muss der Klassenname mithilfe von Klammern ([]) oder mit 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 mit Verweisen auf Common Language Runtime-Module erstellen, ändern und löschen. Sie können diese Verweise in SQL Server jedoch erst ausführen, nachdem Sie die Option clr enabled aktiviert haben. Verwenden Sie sp_configure, um die Option zu aktivieren.

Bewährte Methoden

Im Folgenden werden zwar nicht alle bewährten Methoden aufgeführt, trotzdem können diese Vorschläge zur Verbesserung der Leistung beitragen.

  • Verwenden Sie die SET NOCOUNT ON-Anweisung als erste Anweisung im Textkörper der Prozedur. Setzen Sie sie also direkt hinter das AS-Schlüsselwort. Hierdurch wird das Zurücksenden von Meldungen nach der Ausführung von SELECT-, INSERT-, UPDATE-, MERGE- und DELETE-Anweisungen durch SQL Server an den Client deaktiviert. Die Gesamtleistung der Datenbank und der Anwendung wird durch Vermeiden dieses unnötigen Netzwerkverarbeitungsaufwands verbessert. Weitere Informationen finden Sie unter SET NOCOUNT (Transact-SQL).

  • Verwenden Sie beim Erstellen oder Verweisen auf Datenbankobjekte in der Prozedur Schemanamen. Das Database Engine (Datenbankmodul) kann Objektnamen schneller auflösen, wenn nicht nach mehreren Schemas gesucht werden muss. Zudem werden Probleme hinsichtlich Berechtigung und Zugriff vermieden, die durch das Zuweisen eines Standardschemas eines Benutzers hervorgerufen werden, wenn Objekte ohne Angabe des Schemas erstellt werden. Weitere Informationen finden Sie unter Trennung von Benutzer und Schema.

  • Vermeiden Sie das Umbrechen von Funktionen für in WHERE- und JOIN-Klauseln angegebenen Spalten. Hierdurch werden die Spalten nicht deterministisch, und der Abfrageprozessor kann keine Indizes verwenden.

  • Vermeiden Sie das Verwenden skalarer Funktionen in SELECT-Anweisungen, die viele Datenzeilen zurückgeben. Da die skalare Funktion auf jede Zeile angewendet werden muss, entspricht das sich ergebende Verhalten zeilenbasierter Verarbeitung, und Leistungseinbußen treten auf.

  • Vermeiden Sie die Verwendung von SELECT *. Geben Sie stattdessen die erforderlichen Spaltennamen an. Hierdurch können einige Database Engine (Datenbankmodul)-Fehler vermieden werden, die die Prozedurausführung beenden. Beispiel: Eine SELECT *-Anweisung, die Daten von einer 12-Spaltentabelle zurückgibt und anschließend die Daten in eine temporäre 12-Spaltentabelle einfügt, ist nur so lange erfolgreich, bis die Anzahl oder die Reihenfolge einer der Tabellen geändert wird.

  • Vermeiden Sie das Verarbeiten oder Zurückgeben übermäßig vieler Daten. Schränken Sie die Ergebnisse im Prozedurencode möglichst früh ein, damit alle nachfolgenden von der Prozedur durchgeführten Vorgänge mit dem kleinstmöglichen Dataset durchgeführt werden können. Senden Sie nur die notwendigen Daten an die Clientanwendung. Dies ist effizienter als das Senden zusätzlicher Daten im Netzwerk, wodurch die Clientanwendung unnötig große Resultsets verarbeiten muss.

  • Verwenden Sie explizite Transaktionen durch Verwenden der BEGIN/END TRANSACTION, und halten Sie Transaktionen möglichst kurz. Längere Transaktionen haben das längere Sperren von Datensätzen und eine erhöhte Gefahr von Deadlocking zur Folge. Weitere Informationen finden Sie unter Sperren und Zeilenversionsverwaltung, Kompatibilität von Sperren (Datenbankmodul) oder Isolationsstufen im Datenbankmodul.

  • Verwenden Sie die Transact-SQL TRY…CATCH-Funktion zur Fehlerbehandlung innerhalb einer Prozedur. TRY…CATCH kann einen gesamten Block von Transact-SQL-Anweisungen kapseln. Hierdurch wird nicht nur weniger Verarbeitungsaufwand verursacht, sondern auch die Genauigkeit der Fehlerberichterstattung verbessert und der Programmierungsaufwand verringert. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

  • Verwenden Sie das DEFAULT-Schlüsselwort für alle Tabellenspalten, auf die durch CREATE TABLE- oder ALTER TABLE-Transact-SQL-Anweisungen im Textkörper der Prozedur verwiesen wird. Hierdurch wird NULL nicht an Spalten weitergegeben, von denen keine Nullwerte zugelassen werden.

  • Verwenden Sie für alle Spalten in einer temporären Tabelle NULL oder NOT NULL. 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 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 Prozedur ausführen, mit derselben NULL-Zulässigkeit erstellt.

  • Verwenden Sie Änderungsanweisungen, die Nullen umwandeln, und schließen Sie Logik ein, von der Zeilen mit Nullwerten aus Abfragen gelöscht werden. Beachten Sie, dass in Transact-SQL NULL keine leerer oder "Nichts"-Wert ist. Es handelt sich um einen Platzhalter für einen unbekannten Wert, weshalb unerwartetes Verhalten auftreten kann, besonders beim Abfragen von Resultsets oder Verwenden von AGGREGATE-Funktionen. Weitere Informationen finden Sie unter Suchbedingungen mit NULL-Vergleichen und NULL-Werte.

  • Verwenden Sie den UNION ALL-Operator statt den UNION- oder OR-Operatoren, sofern nicht unbedingt unterschiedliche Werte erforderlich sind. Der UNION ALL-Operator erfordert weniger Verarbeitungsaufwand, da aus dem Resultset keine Duplikate gefiltert werden.

Allgemeine Hinweise

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

Variablen innerhalb der Prozedur können benutzerdefinierte oder Systemvariablen (z. B. @@SPID) sein.

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 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 oder Ausführung von gespeicherten Prozeduren und Triggern.

Eine oder mehrere Prozeduren können beim Start von SQL Server automatisch ausgeführt werden. Die 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 Ausführen von gespeicherten Prozeduren (Datenbankmodul).

Prozeduren sind geschachtelt, wenn eine Prozedur eine andere Prozedur aufruft oder verwalteten Code durch Verweisen auf eine CLR-Routine, einen -Typ oder ein -Aggregat ausführt. Sie können Prozeduren und Verweise auf verwalteten Code bis auf 32 Ebenen schachteln. Die Schachtelungsebene wird um eine Ebene erhöht, wenn die aufgerufene Prozedur oder der Verweis auf verwalteten Code die Ausführung beginnt, und um eine Ebene verringert, wenn die aufgerufene Prozedur oder der Verweis auf verwalteten Code die Ausführung beendet. Methoden, die innerhalb des verwalteten Codes aufgerufen wurden, werden nicht auf diese Grenze für Schachtelungsebenen angerechnet. Wenn jedoch eine gespeicherte CLR-Prozedur Datenzugriffsvorgänge über den von SQL Server verwalteten Anbieter ausführt, werden zusätzliche Schachtelungsebenen im Übergang aus verwaltetem Code in SQL hinzugefügt.

Der Versuch, die Anzahl der maximalen Schachtelungsebenen zu überschreiten, führt dazu, dass die gesamte Aufrufkette einen Fehler erzeugt. Sie können die @@NESTLEVEL-Funktion verwenden, um die Schachtelungsebene für die zurzeit ausgeführte gespeicherte Prozedur zu speichern.

Interoperabilität

Database Engine (Datenbankmodul) speichert die Einstellungen sowohl für SET QUOTED_IDENTIFIER als auch für SET ANSI_NULLS, wenn eine 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 Prozedur ignoriert.

Andere SET-Optionen, wie z. B. SET ARITHABORT, SET ANSI_WARNINGS oder SET ANSI_PADDINGS, werden nicht gespeichert, wenn eine Prozedur erstellt oder geändert wird. Wenn die Logik der 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 Prozedur heraus ausgeführt wird, bleibt die betreffende Einstellung nur so lange in Kraft, bis die Ausführung der Prozedur abgeschlossen ist. Die Einstellung wird dann mit dem Wert wiederhergestellt, den sie hatte, als die Prozedur aufgerufen wurde. Dies gibt einzelnen Clients die Möglichkeit, die gewünschten Optionen festzulegen, ohne die Logik der Prozedur zu beeinflussen.

Alle SET-Anweisungen können in einer 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 Prozedur in Kraft und wird dann auf die vorherige Einstellung zurückgesetzt. Weitere Informationen zu SET-Optionen finden Sie unter SET-Optionen.

HinweisHinweis

SET ANSI_WARNINGS wird beim Übergeben von Parametern in einer 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 Anweisung INSERT oder UPDATE wird erfolgreich ausgeführt.

Einschränkungen

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

Folgende Anweisungen können nicht an einer beliebigen Stelle im Textkörper einer gespeicherten Prozedur verwendet werden.

CREATE AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT

CREATE DEFAULT

CREATE oder ALTER TRIGGER

SET SHOWPLAN_XML

CREATE oder ALTER FUNCTION

CREATE oder ALTER VIEW

USE database_name

CREATE oder ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

Eine Prozedur kann auf noch nicht vorhandene Tabellen verweisen. Zum Zeitpunkt der Erstellung wird nur die Syntaxüberprüfung ausgeführt. Die 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 Prozedur verwiesen wird. Daher kann eine syntaktisch richtige Prozedur, die auf noch nicht vorhandene Tabellen verweist, erfolgreich erstellt werden. Die Prozedur schlägt jedoch zur Ausführungszeit fehl, wenn die Tabellen, auf die verwiesen wird, nicht vorhanden sind. Weitere Informationen finden Sie unter Verzögerte Namensauflösung und Kompilierung.

Sie können einen Funktionsnamen nicht als Parameterwert oder den Wert angeben, der beim Ausführen einer Prozedur an einen Parameter weitergegeben wird. Sie können eine Funktion aber auch wie im folgenden Beispiel gezeigt als Variable übergeben.

-- Passing the function value as a variable.
DECLARE @CheckDate datetime = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; 
GO

Wenn die Prozedur Änderungen an einer Remoteinstanz von SQL Server vornimmt, kann für diese Änderungen kein Rollback ausgeführt werden. Remoteprozeduren nehmen nicht an Transaktionen teil. Weitere Informationen finden Sie unter Behandeln von Fehlern von remote gespeicherten Server-zu-Server-Prozeduren.

Die in der EXTERNAL NAME-Klausel 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 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.

Metadaten

In der folgenden Tabelle sind die Katalogsichten und dynamischen Verwaltungssichten aufgeführt, die Sie verwenden können, um Informationen zu gespeicherten Prozeduren zurückzugeben.

Ansicht

Beschreibung

sys.sql_modules

Gibt die Definition einer Transact-SQL-Prozedur zurück. Der Text einer mit der ENCRYPTION-Option erstellten Prozedur kann nicht mit der sys.sql_modules-Katalogsicht angezeigt werden.

sys.assembly_modules

Gibt Informationen zu einer CLR-Prozedur zurück.

sys.parameters

Gibt Informationen über die Parameter zurück, die in einer Prozedur definiert sind.

sys.sql_expression_dependenciessys.dm_sql_referenced_entitiessys.dm_sql_referencing_entities

Gibt die Objekte zurück, auf die eine Prozedur verweist.

Verwenden Sie die Leistungsindikatoren Cachetrefferquote, Cacheseiten und Cacheobjektzähler, um die Größe einer kompilierten Prozedur zu schätzen. Weitere Informationen finden Sie unter SQL Server, Plancache-Objekt.

Sicherheit

Berechtigungen

Erfordert die CREATE PROCEDURE-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema, in der die Prozedur erstellt wird, oder erfordert die Mitgliedschaft in der festen Datenbankrolle db_ddladmin.

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

Beispiele

Kategorie

Funktionssyntaxelemente

Grundlegende Syntax

CREATE PROCEDURE

Übergeben von Parametern

@parameter • = default • OUTPUT • Tabellenwertparameter-Typ • CURSOR VARYING

Ändern von Daten mithilfe einer gespeicherten Prozedur

UPDATE

Fehlerbehandlung

TRY…CATCH

Verbergen der Prozedurdefinition

WITH ENCRYPTION

Erzwingen der erneuten Kompilierung der Prozedur

WITH RECOMPILE

Festlegen des Sicherheitskontexts

EXECUTE AS

Grundlegende Syntax

Anhand von Beispielen in diesem Abschnitt wird die grundlegende Funktion der CREATE PROCEDURE-Anweisung mithilfe der mindestens erforderlichen Syntax veranschaulicht.

A. Erstellen einer einfachen Transact-SQL-Prozedur

Im folgenden Beispiel wird eine gespeicherte Prozedur erstellt, die alle Mitarbeiter (mit Vor- und Nachnamen), ihre Titel und ihre Abteilungsnamen aus einer Sicht zurückgibt. Diese Prozedur verwendet keine Parameter. Das Beispiel zeigt dann die drei Methoden für das Ausführen der Prozedur.

USE AdventureWorks2008R2;
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 uspGetEmployees-Prozedur 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. Zurückgeben von mehreren Resultsets

Die folgende Prozedur gibt zwei Resultsets zurück.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.uspMultipleResults 
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

C. Erstellen einer gespeicherten CLR-Prozedur

Im folgenden Beispiel wird die GetPhotoFromDB-Prozedur erstellt, die auf die GetPhotoFromDB-Methode der LargeObjectBinary-Klasse in der HandlingLOBUsingCLR -Assembly verweist. Bevor die 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

Übergeben von Parametern

Die Beispiele in diesem Abschnitt veranschaulichen, wie die Eingabe- und Ausgabeparameter zum Übergeben von Werten von und an eine gespeicherte Prozedur verwendet werden.

A. Erstellen einer Prozedur mit Eingabeparametern

Im folgenden Beispiel wird eine gespeicherte Prozedur erstellt, die Informationen für einen bestimmten Mitarbeiter zurückgibt, indem Werte für den Vor- und Nachnamen des Mitarbeiters übergeben werden. Diese Prozedur nimmt nur genaue Übereinstimmungen für die übergebenen Parameter an.

USE AdventureWorks2008R2;
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 uspGetEmployees-Prozedur 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';

B. Verwenden einer Prozedur mit Platzhalterparametern

Im folgenden Beispiel wird eine gespeicherte Prozedur erstellt, die Informationen für Mitarbeiter zurückgibt, indem vollständige oder Teilwerte für den Vor- und Nachnamen des Mitarbeiters übergeben werden. Diese 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 AdventureWorks2008R2;
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 uspGetEmployees2-Prozedur kann in verschiedenen Kombinationen ausgeführt werden. Hier werden nur einige mögliche 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%';

C. Verwenden von OUTPUT-Parametern

Im folgenden Beispiel wird die uspGetList-Prozedur 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 AdventureWorks2008R2;
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.

D. Verwenden eines Tabellenwertparameters

Im folgenden Beispiel wird ein Tabellenwertparameter verwendet, um mehrere Zeilen in eine Tabelle einzufügen. Der Parametertyp wird erstellt und eine Tabellenvariable deklariert, die darauf verweist. Außerdem werden Daten in die Parameterliste eingefügt und die Werte dann an eine gespeicherte Prozedur übergeben. Die gespeicherte Prozedur verwendet die Werte, um mehrere Zeilen in eine Tabelle einzufügen.

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

E. Verwenden eines OUTPUT-Cursorparameters

Im folgenden Beispiel wird der OUTPUT-Cursorparameter verwendet, um einen Cursor aus einer Prozedur an den aufrufenden Batch, die aufrufende 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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

Ändern von Daten mithilfe einer gespeicherten Prozedur

In den Beispielen in diesem Abschnitt wird gezeigt, wie Daten in Tabellen oder Sichten eingefügt oder geändert werden, indem eine DML-Anweisung (Data Manipulation Language, Datenbearbeitungssprache) in die Definition der Prozedur eingeschlossen wird.

A. Verwenden von UPDATE in einer gespeicherten Prozedur

Im folgenden Beispiel wird eine UPDATE-Anweisung in einer gespeicherten Prozedur verwendet. Die Prozedur erfordert den Eingabeparameter @NewHours und den Ausgabeparameter @RowCount. Der Parameterwert @NewHours wird in der UPDATE-Anweisung verwendet, um die Spalte VacationHours in der Tabelle HumanResources.Employee zu aktualisieren. Der Ausgabeparameter @RowCount wird verwendet, um die Anzahl betroffener Zeilen an eine lokale Variable zurückzugeben. Ein CASE-Ausdruck wird in der SET-Klausel verwendet, um den Wert, der für VacationHours festgelegt wird, bedingt zu bestimmen. Wenn der Mitarbeiter pro Stunde bezahlt wird (SalariedFlag = 0), ist VacationHours auf die aktuelle Anzahl der Stunden zuzüglich des Werts festgelegt, der unter @NewHours angegeben ist. Andernfalls ist VacationHours auf den Wert festgelegt, der unter @NewHours angegeben ist.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

Fehlerbehandlung

Die Beispiele in diesem Abschnitt veranschaulichen Methoden zur Behandlung von Fehlern, die bei der Ausführung der gespeicherten Prozedur auftreten können.

Verwenden von TRY…CATCH

Im folgenden Beispiel wird das TRY…CATCH-Konstrukt verwendet, um Fehlerinformationen zurückzugeben, die während der Ausführung einer gespeicherten Prozedur erfasst wurden.

USE AdventureWorks2008R2;
GO

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS
SET NOCOUNT ON;
BEGIN TRY
   BEGIN TRANSACTION 
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;

/* Intentionally generate an error by reversing the order in which rows are deleted from the
   parent and child tables. This change does not cause an error when the procedure
   definition is altered, but produces an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS

BEGIN TRY
   BEGIN TRANSACTION 
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT TRANSACTION

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;

DROP PROCEDURE Production.uspDeleteWorkOrder;

Verbergen der Prozedurdefinition

In den Beispielen dieses Abschnitts wird gezeigt, wie die Definition der gespeicherten Prozedur verborgen wird.

A. Verwenden der WITH ENCRYPTION-Option

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

USE AdventureWorks2008R2;
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 BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

Die WITH ENCRYPTION-Option verbirgt die Definition der Prozedur bei Abfragen des Systemkatalogs oder bei Verwenden von Metadatenfunktionen, 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 AdventureWorks2008R2;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Dies ist das Resultset.

definition

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

NULL

Erzwingen der erneuten Kompilierung der Prozedur

In den Beispielen dieses Abschnitts wird die WITH RECOMPILE-Klausel verwendet, um das erneute Kompilieren der Prozedur bei jeder Ausführung zu erzwingen.

A. 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 AdventureWorks2008R2;
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.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

Festlegen des Sicherheitskontexts

Die Beispiele dieses Abschnitts verwenden die EXECUTE AS-Klausel zum Festlegen des Sicherheitskontexts, in dem die gespeicherte Prozedur ausgeführt wird.

A. Verwenden der EXECUTE AS-Klausel

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

USE AdventureWorks2008R2;
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 '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

B. Erstellen benutzerdefinierter Berechtigungssätze

Im folgenden Beispiel wird EXECUTE AS verwendet, um benutzerdefinierte Berechtigungen für einen Datenbankvorgang zu erstellen. Einige Vorgänge, z. B. TRUNCATE TABLE, besitzen keine Berechtigungen, die gewährt werden können. Durch die Aufnahme der TRUNCATE TABLE-Anweisung innerhalb einer gespeicherten Prozedur und durch die Festlegung, dass die Prozedur als Benutzer mit der Berechtigung zur Tabellenänderung ausgeführt wird, können Sie die Berechtigungen zum Abschneiden der Tabelle auf alle Benutzer ausdehnen, denen Sie die EXECUTE-Berechtigungen für die Prozedur erteilen. Weitere Informationen finden Sie unter Verwenden von EXECUTE AS zum Erstellen benutzerdefinierter Berechtigungssätze.

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Siehe auch

Verweis

ALTER 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)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

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

Erstellen von gespeicherten Prozeduren (Datenbankmodul)

Tabellenwertparameter (Datenbankmodul)

Andere Ressourcen

Gespeicherte Prozeduren (Datenbankmodul)