CREATE PROCEDURE (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Hiermit wird eine gespeicherte Transact-SQL- oder CLR-Prozedur (Common Language Runtime) in SQL Server, Azure SQL-Datenbank und Analytics Platform System (PDW) erstellt. 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 Vorgänge 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 Fehlschlagen (sowie die Ursache für das Fehlschlagen) anzeigt.

Mithilfe dieser Anweisung können Sie eine dauerhafte Prozedur in der aktuellen Datenbank oder eine temporäre Prozedur in der tempdb-Datenbank erstellen.

Hinweis

Die Integration der .NET Framework-CLR in SQL Server wird in diesem Thema erläutert. Die CLR-Integration gilt nicht für Azure SQL-Datenbank.

Wenn Sie die ausführlichen Informationen zur Syntax überspringen und ein Beispiel für eine einfache gespeicherte Prozedur möchten, fahren Sie einfach mit dem Abschnitt Einfache Beispiele fort.

Transact-SQL-Syntaxkonventionen

Syntax

Transact-SQL-Syntax für gespeicherte Prozeduren in SQL Server und Azure SQL Datenbank:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

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

Transact-SQL-Syntax für gespeicherte CLR-Prozeduren:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Transact-SQL-Syntax für nativ kompilierte gespeicherte Prozeduren:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]

<set_option> ::=
    LANGUAGE = [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]

Transact-SQL-Syntax für gespeicherte Prozeduren in Azure Synapse Analytics und Parallel Data Warehouse:

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Transact-SQL-Syntax für gespeicherte Prozeduren in Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Argumente

OR ALTER

Gilt für: Azure SQL-Datenbank, SQL Server (ab SQL Server 2016 (13.x) SP1).

Ändert die Prozedur, wenn sie bereits vorhanden ist.

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.

procedure_name

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

Achtung

Beim Benennen von Prozeduren sollten Sie das Präfix sp_ vermeiden. 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.

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 ( ##procedure_name) für globale temporäre Prozeduren vorangestellt wird. Eine lokale temporäre Prozedur ist nur für die Verbindung sichtbar, von der sie erstellt wurde. 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. Für CLR-Prozeduren können keine temporären Namen 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

Gilt für: SQL Server 2008 (10.0.x) und höher sowie Azure SQL-Datenbank

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.

Hinweis

Diese Funktion wird in einer zukünftigen Version von SQL Serverentfernt. Nutzen Sie diese Funktionen bei Neuentwicklungen nicht mehr, und planen Sie die Änderung von Anwendungen, die diese Funktion zurzeit verwenden.

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

@parameter_name

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 (maximal 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. Wenn eine Prozedur Tabellenwertparameter enthält und der Parameter im Aufruf fehlt, wird eine leere Tabelle übergeben. Parameter können 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 Datenbanktyp gehört.

Richtlinien 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 Verwenden von Tabellenwertparametern (Datenbank-Engine).
• cursor-Datentypen können nur OUTPUT-Parameter sein und müssen vom VARYING-Schlüsselwort begleitet werden.

Richtlinien für CLR-Prozeduren:

• Alle systemeignen 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-Systemdatentypen 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 diesen 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.

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. Die Parameter text, ntext und image können nicht als OUTPUT-Parameter verwendet werden, es sei denn, es handelt sich bei der Prozedur um eine CLR-Prozedur. Ein Ausgabeparameter kann ein Cursorplatzhalter sein, sofern die Prozedur keine CLR-Prozedur ist. 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 die Datenbank-Engine 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 einer gespeicherten Prozedur. Diese Option kann nicht für CLR-Prozeduren verwendet werden, wenn FOR REPLICATION angegeben ist.

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

ENCRYPTION

Gilt für: SQL Server (SQL Server 2008 (10.0.x) und höher), Azure SQL-Datenbank

Gibt an, dass SQL Server den Originaltext der CREATE PROCEDURE-Anweisung in ein verborgenes Format umwandelt. Die Ausgabe der Obfuskation ist nicht direkt in den Katalogsichten in SQL Server sichtbar. Benutzer, die keinen Zugriff auf Systemtabellen oder Datenbankdateien haben, können den verschleierten Text nicht abrufen. Der Text ist jedoch für berechtigte Benutzer 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 zu Berechtigungen 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-Klausel

Gibt den Sicherheitskontext an, unter dem die Prozedur ausgeführt wird.

Für nativ kompilierte gespeicherte Prozeduren, die mit SQL Server 2016 (13.x) und Azure SQL-Datenbank beginnen, bestehen keine Einschränkungen für die EXECUTE AS-Klausel. In SQL Server 2014 (12.x) werden die SELF-, OWNER- und 'user_name' -Klauseln bei nativ kompilierten gespeicherten Prozeduren unterstützt.

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

FOR REPLICATION

Gilt für: SQL Server (SQL Server 2008 (10.0.x) und höher), Azure SQL-Datenbank

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 verfügt über 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

Gilt für: SQL Server 2008 (10.0.x) und höher, SQL-Datenbank

Gibt für eine CLR-Prozedur, auf die verwiesen wird, die Methode einer .NET Framework-Assembly an. class_name muss ein gültiger SQL Server-Bezeichner 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, 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 dazu sp_configure.

Hinweis

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

ATOMIC WITH

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Gibt die unteilbare Ausführung der gespeicherten Prozedur an. Änderungen werden entweder über ein Commit ausgeführt, oder ein Rollback aller Änderungen wird durch eine Ausnahme auslöst. Der ATOMIC WITH-Block ist für systemintern kompilierte gespeicherte Prozeduren erforderlich.

Wenn die Prozedur ein RETURN zurückgibt (explizit durch die RETURN-Anweisung oder implizit durch eine Ausführung), wird für von der Prozedur durchgeführte Arbeiten ein Commit ausgeführt. Wenn die Prozedur ein THROW zurückgibt, wird für von der Prozedur durchgeführte Arbeiten ein Rollback ausgeführt.

XACT_ABORT ist standardmäßig innerhalb eines ATOMIC-Blocks aktiviert und kann nicht geändert werden. XACT_ABORT gibt an, ob SQL Server für die aktuelle Transaktion automatisch ein Rollback ausführt, wenn eine Transact-SQL-Anweisung einen Laufzeitfehler ausgibt.

Die folgenden SET-Optionen sind im ATOMIC-Block stets aktiviert und können nicht geändert werden.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

SET-Optionen können innerhalb von ATOMIC-Blöcken nicht geändert werden. Die SET-Optionen in der Benutzersitzung werden nicht im Kontext der nativ kompilierten gespeicherten Prozeduren verwendet. Diese Optionen werden zur Kompilierzeit korrigiert.

BEGIN-, ROLLBACK- und COMMIT-Vorgänge können nicht innerhalb eines ATOMIC-Blocks verwendet werden.

Es gibt einen ATOMIC-Block für jede systemintern kompilierte gespeicherte Prozedur im äußeren Bereich der Prozedur. Die Blöcke können nicht geschachtelt werden. Weitere Informationen zu nativ kompilierten gespeicherten Prozeduren finden Sie unter Nativ kompilierte gespeicherte Prozeduren.

NULL | NOT NULL

Bestimmt, ob NULL-Werte in einem Parameter zulässig sind. NULL ist die Standardeinstellung.

NATIVE_COMPILATION

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Gibt an, dass die Prozedur systemintern kompiliert wird. NATIVE_COMPILATION, SCHEMABINDING und EXECUTE AS können in beliebiger Reihenfolge angegeben werden. Weitere Informationen finden Sie unter Nativ kompilierte gespeicherte Prozeduren.

SCHEMABINDING

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Stellt sicher, dass Tabellen, auf die eine Prozedur verweist, nicht gelöscht oder geändert werden können. SCHEMABINDING ist in systemintern kompilierten gespeicherten Prozeduren erforderlich. Weitere Informationen finden Sie unter Nativ kompilierte gespeicherte Prozeduren. Es gelten dieselben SCHEMABINDING-Einschränkungen wie für benutzerdefinierte Funktionen. Weitere Informationen finden Sie im Abschnitt „SCHEMABINDING“ unter CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Entspricht der Sitzungsoption SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'Sprache' ist erforderlich.

TRANSACTION ISOLATION LEVEL

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Erforderlich für systemintern kompilierte gespeicherte Prozeduren. Gibt die Transaktionsisolationsstufe für die gespeicherte Prozedur an. Die folgenden Optionen sind verfügbar:

Weitere Informationen zu diesen Optionen finden Sie unter SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READ

Gibt an, dass Anweisungen Daten nicht lesen können, die von anderen Transaktionen geändert wurden, für die jedoch noch kein Commit ausgeführt wurde. Wenn eine andere Transaktion Daten modifiziert, die von der aktuellen Transaktion gelesen wurden, schlägt die aktuelle Transaktion fehl.

SERIALIZABLE

Gibt Folgendes an:

• Anweisungen können keine Daten lesen, die geändert wurden, für die jedoch noch kein Commit von anderen Transaktionen ausgeführt wurde.
• Wenn eine andere Transaktion Daten modifiziert, die von der aktuellen Transaktion gelesen wurden, schlägt die aktuelle Transaktion fehl.
• Wenn eine andere Transaktion neue Zeilen mit Schlüsselwerten einfügt, die in den von Anweisungen in der aktuellen Transaktion gelesenen Schlüsselbereich fallen, schlägt die aktuelle Transaktion fehl.

SNAPSHOT

Gibt an, dass von Anweisungen in einer Transaktion gelesene Daten der im Hinblick auf Transaktionen konsistenten Version der Daten entsprechen, die zu Beginn der Transaktion vorhanden waren.

DATEFIRST = number

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Legt den ersten Wochentag auf eine Zahl von 1 bis 7 fest. DATEFIRST ist optional. Wenn dies nicht angegeben ist, wird die Einstellung von der angegebenen Sprache abgeleitet.

Weitere Informationen finden Sie unter SET DATEFIRST (Transact-SQL).

DATEFORMAT = format

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

Legt die Reihenfolge der Datumsteile für den Tag, den Monat und das Jahr fest, um die date-, smalldatetime-, datetime-, datetime2- und datetimeoffset-Zeichenfolgen zu interpretieren. DATEFORMAT ist optional. Wenn dies nicht angegeben ist, wird die Einstellung von der angegebenen Sprache abgeleitet.

Weitere Informationen finden Sie unter SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }

Gilt für: SQL Server 2014 (12.x) und höher sowie Azure SQL-Datenbank

SQL Server-Transaktionscommits können entweder vollständig dauerhaft (Standardeinstellung) oder verzögert dauerhaft sein.

Weitere Informationen finden Sie im Thema Steuern der Transaktionsdauerhaftigkeit.

Einfache Beispiele

Hier finden Sie zwei kurze Beispiele: SELECT DB_NAME() AS ThisDB; gibt den Namen der aktuellen Datenbank zurück. Sie können diese Anweisung mit einer gespeicherten Prozedur umschließen wie z.B.:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Diese gespeicherte Prozedur lässt sich mit der Anweisung EXEC What_DB_is_this; aufrufen.

Sie ist etwas komplexer und dient dazu, einen Eingabeparameter bereitzustellen, um die Prozedur flexibler zu gestalten. Beispiel:

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;

Geben Sie eine Datenbank-ID an, wenn Sie die Prozedur aufrufen. EXEC What_DB_is_that 2; gibt beispielsweise tempdb zurück.

Weitere Beispiele finden Sie unter Beispiele am Ende dieses Artikels.

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. Dadurch wird die erzeugte Ausgabe zur besseren Übersichtlichkeit auf ein Minimum reduziert. Für die Hardware von heute ergibt sich jedoch kein messbarer Leistungsvorteil. Weitere Informationen finden Sie unter SET NOCOUNT (Transact-SQL).
• Verwenden Sie beim Erstellen oder Verweisen auf Datenbankobjekte in der Prozedur Schemanamen. Die Datenbank-Engine kann Objektnamen schneller auflösen, wenn nicht mehrere Schemas durchsucht werden müssen. 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.
• Verwenden Sie keine Wrapperfunktionen für Spalten, die in den WHERE- und JOIN-Klauseln angegeben sind. 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 resultierende 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 Datenbank-Engine-Fehler vermieden werden, die die Prozedurausführung beenden. Beispiel: Eine SELECT *-Anweisung, die Daten aus einer Tabelle mit 12 Spalten zurückgibt und anschließend die Daten in eine temporäre Tabelle mit 12 Spalten einfügt, wird nur so lange erfolgreich ausgeführt, bis die Anzahl oder die Reihenfolge der Spalten in 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 von BEGIN/COMMIT TRANSACTION, und halten Sie Transaktionen möglichst kurz. Längere Transaktionen führen dazu, dass Datensätze länger gesperrt sind und die Wahrscheinlichkeit von Deadlocks steigt.
• Verwenden Sie das Transact-SQL-Feature „TRY…CATCH“ 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.
• 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 übergeben, von denen keine NULL-Werte 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 Datenbank-Engine 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 NULL-Werte umwandeln, und schließen Sie Logik ein, von der Zeilen mit NULL-Werten aus Abfragen gelöscht werden. Beachten Sie, dass NULL in Transact-SQL kein 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.
• Verwenden Sie den UNION ALL-Operator anstelle des UNION- oder OR-Operators, sofern nicht unbedingt unterschiedliche Werte erforderlich sind. Der UNION ALL-Operator erfordert weniger Verarbeitungsaufwand, da aus dem Resultset keine Duplikate herausgefiltert werden.

Hinweise

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

Innerhalb der Prozedur angegebene Variablen können benutzerdefinierte Variablen 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 des Datenbank-Engines vorhanden ist.

Mindestens eine Prozedur kann beim Start von SQL Server automatisch ausgeführt werden. Die Prozeduren müssen vom Systemadministrator in der master 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 einer gespeicherten Prozedur.

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 tief 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 beim Übergang von verwaltetem Code zu SQL zusätzliche Schachtelungsebenen hinzugefügt.

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

Interoperabilität

Die Datenbank-Engine speichert die Einstellungen sowohl für SET QUOTED_IDENTIFIER als auch für SET ANSI_NULLS, wenn eine Transact-SQL-Prozedur erstellt oder geändert wird. Diese Originaleinstellungen werden verwendet, wenn die 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.

Beliebige SET-Anweisungen können in einer Prozedur angegeben werden, mit Ausnahme von SET SHOWPLAN_TEXT und SET SHOWPLAN_ALL. Diese 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.

Hinweis

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	SET	USE
CREATE AGGREGATE	SET SHOWPLAN_TEXT	USE Name der Datenbank
CREATE DEFAULT	Mit SET SHOWPLAN_XML
CREATE RULE	SET PARSEONLY
CREATE SCHEMA	SET SHOWPLAN_ALL
CREATE oder ALTER TRIGGER
CREATE oder ALTER FUNCTION
CREATE oder ALTER PROCEDURE
CREATE oder ALTER VIEW

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.

Sie können einen Funktionsnamen nicht als Parameterstandardwert 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.

Die in der EXTERNAL NAME-Klausel angegebene Methode muss die folgenden Merkmale aufweisen, damit das Datenbank-Engine 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 in der Prozedur enthalten sind.
• 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.

Sicht	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_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Gibt die Objekte zurück, auf die eine Prozedur verweist.

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

Name des Systemmonitorobjekts	Indikatorname des Systemmonitors
SQLServer: Plancache-Objekt	Cachetrefferquote
	Cacheseiten
	Cacheobjektzähler 1

¹ Diese Zähler sind für verschiedene Kategorien von Cacheobjekten verfügbar (einschließlich Ad-hoc-Transact-SQL-Anweisungen, vorbereiteten Transact-SQL-Anweisungen, Prozeduren, Triggern usw.). Weitere Informationen finden Sie unter SQL Server, Plancache-Objekt.

Berechtigungen

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

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

CREATE PROCEDURE und speicheroptimierte Tabellen

Auf speicheroptimierte Tabellen kann am effizientesten über traditionell und nativ kompilierte gespeicherte Prozeduren zugegriffen werden. Native Prozeduren sind in den meisten Fällen die effizientere Methode. Weitere Informationen finden Sie unter Nativ kompilierte gespeicherte Prozeduren.

Im folgenden Beispiel wird dargestellt, wie eine nativ kompilierte gespeicherte Prozedur erstellt wird, die auf die speicheroptimierte Tabelle dbo.Departments zugreift:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO

Eine Prozedur, die ohne NATIVE_COMPILATION erstellt wurde, kann nicht in eine systemintern kompilierte gespeicherten Prozedur geändert werden.

Eine Besprechung der Programmierbarkeit von nativ kompilierten gespeicherten Prozeduren, dem unterstützten Abfragenoberflächenbereich und der Operatoren finden Sie unter Unterstützte Features für nativ kompilierte T-SQL-Module.

Beispiele

Category	Funktionssyntaxelemente
Grundlegende Syntax	CREATE PROCEDURE
Übergeben von Parametern	@parameter = default AUSGABE Parametertyp mit Tabellenwert 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 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 in der AdventureWorks2022-Datenbank zurückgibt. Diese Prozedur verwendet keine Parameter. Das Beispiel zeigt dann die drei Methoden für das Ausführen der Prozedur.

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

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 mehrerer Resultsets

Die folgende Prozedur gibt zwei Resultsets zurück.

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.

Gilt für: SQL Server 2008 (10.0.x) und höher, SQL-Datenbank (wenn eine aus assembly_bits erstellte Assembly verwendet wird)

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.

D: 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 akzeptiert nur genaue Übereinstimmungen für die übergebenen Parameter an.

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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';

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

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

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%';

F. 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 eine Gruppe von Werten während der Ausführung der Prozedur.

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 Sprachkonstrukten zur Ablaufsteuerung verwendet, um eine Meldung an das Fenster Meldungen zurückzugeben.

Hinweis

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.

G. 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 auf ihn 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 Zeile in eine Tabelle einzufügen.

/* 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 [AdventureWorks2022].[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
    [AdventureWorks2022].[Person].[StateProvince];

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

H. 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:

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 Cursorvariable deklariert. Dann wird die Prozedur ausgeführt, um der lokalen Variablen den Cursor zuzuordnen, und zuletzt werden die Zeilen aus dem Cursor abgerufen.

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.

I. 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 @NewHours-Parameterwert 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.

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

Fehlerbehandlung

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

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

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;
GO
/* 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;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Verschleiern der Prozedurdefinition

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

K. Verwenden der Option WITH ENCRYPTION

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

Gilt für: SQL Server 2008 (10.0.x) und höher sowie Azure SQL-Datenbank

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber,
        VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

Die Option WITH ENCRYPTION verbirgt die Definition der Prozedur bei Abfragen des Systemkatalogs oder bei Verwenden von Metadatenfunktionen, wie in den folgenden Beispielen gezeigt wird.

Führen Sie sp_helptext aus:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Hier sehen Sie das Ergebnis.

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

Fragen Sie die sys.sql_modules-Katalogsicht direkt ab:

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

Hier sehen Sie das Ergebnis.

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

Hinweis

Die gespeicherte Systemprozedur sp_helptext wird in Azure Synapse Analytics nicht unterstützt. Verwenden Sie stattdessen die sys.sql_modules-Objektkatalogsicht.

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.

L. Verwenden der Option WITH RECOMPILE

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.

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;

Festlegen des Sicherheitskontexts

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

M. Verwenden der Klausel EXECUTE AS

Im folgenden Beispiel wird die Verwendung der EXECUTE AS-Klausel gezeigt, um den Sicherheitskontext anzugeben, in dem eine Prozedur ausgeführt werden kann. Im Beispiel gibt die Option CALLER an, dass die Prozedur im Kontext des Benutzers, der die Prozedur aufruft, ausgeführt werden kann.

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

N. 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. Indem Sie die TRUNCATE TABLE-Anweisung in eine gespeicherte Prozedur aufnehmen und festlegen, 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.

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

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

O. Erstellen einer gespeicherten Prozedur, die eine SELECT-Anweisung ausführt

In diesem Beispiel wird die grundlegende Syntax zum Erstellen und Ausführen einer Prozedur gezeigt. Beim Ausführen eines Batchs muss CREATE PROCEDURE in der ersten Anweisung enthalten sein. Um beispielsweise die folgende gespeicherte Prozedur in AdventureWorksPDW2022 zu erstellen, legen Sie zuerst den Datenbankkontext fest, und führen Sie dann die CREATE PROCEDURE-Anweisung aus.

-- Uses AdventureWorksDW database

--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
    SELECT TOP (10) r.ResellerName, r.AnnualSales
    FROM DimReseller AS r
    ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO

--Show 10 Top Resellers
EXEC Get10TopResellers;

Siehe auch

• ALTER PROCEDURE (Transact-SQL)
• Ablaufsteuerungssprache (Transact-SQL)
• Cursor
• Datentypen (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• EXECUTE AS (Transact-SQL)
• Gespeicherte Prozeduren (Datenbank-Engine)
• 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)
• Erstellen einer gespeicherten Prozedur
• Verwenden von Tabellenwertparameter (Datenbank-Engine)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)