Gespeicherte Prozeduren (Datenbank-Engine)Stored Procedures (Database Engine)

Dieses Thema gilt für: JaSQL ServerJaAzure SQL-DatenbankJaAzure SQL Data Warehouse Ja Parallel DatawarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Eine gespeicherte Prozedur in SQL ServerSQL Server entspricht einer oder mehreren Transact-SQLTransact-SQL-Anweisungen oder einem Verweis auf eine MicrosoftMicrosoft .NET Framework.NET Framework CLR-Methode (Common Language Runtime).A stored procedure in SQL ServerSQL Server is a group of one or more Transact-SQLTransact-SQL statements or a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method. Prozeduren sind mit Konstrukten anderer Programmiersprachen vergleichbar, da sie folgende Fähigkeiten aufweisen:Procedures resemble constructs in other programming languages because they can:

  • Annehmen von Eingabeparametern und Zurückgeben mehrerer Werte in Form von Ausgabeparametern an das aufrufende Programm.Accept input parameters and return multiple values in the form of output parameters to the calling program.

  • Aufnehmen von Programmierungsanweisungen, die Vorgänge in der Datenbank ausführen.Contain programming statements that perform operations in the database. Dies schließt auch den Aufruf anderer Prozeduren ein.These include calling other procedures.

  • Zurückgeben eines Statuswerts an ein aufrufendes Programm, der Erfolg oder Misserfolg (sowie die Ursache für den Misserfolg) anzeigt.Return a status value to a calling program to indicate success or failure (and the reason for failure).

Vorteile bei der Verwendung gespeicherter ProzedurenBenefits of Using Stored Procedures

In der folgenden Liste sind einige Vorteile bei der Verwendung von Prozeduren beschrieben.The following list describes some benefits of using procedures.

Verringerter Netzwerkdatenverkehr zwischen Server und ClientReduced server/client network traffic
Die Befehle in einer Prozedur werden als einzelner Codebatch ausgeführt.The commands in a procedure are executed as a single batch of code. Dadurch lässt sich der Netzwerkdatenverkehr zwischen Server und Client erheblich reduzieren, da nur der Aufruf zum Ausführen der Prozedur über das Netzwerk gesendet wird.This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Wenn der Code nicht von einer Prozedur gekapselt würde, müsste jede einzelne Codezeile über das Netzwerk übertragen werden.Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

Höhere SicherheitStronger security
Mithilfe einer Prozedur können mehrere Benutzer und Clientprogramme Vorgänge für zugrunde liegende Datenbankobjekte ausführen, selbst wenn die Benutzer und Programme keine direkte Berechtigungen für diese zugrunde liegenden Objekte aufweisen.Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. Die Prozedur steuert, welche Prozesse und Aktivitäten ausgeführt werden, und schützt die zugrunde liegenden Datenbankobjekte.The procedure controls what processes and activities are performed and protects the underlying database objects. Dadurch ist es nicht erforderlich, Berechtigungen auf der individuellen Objektebene zu gewähren, was zu einer Vereinfachung der Sicherheitsebenen führt.This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

Die EXECUTE AS -Klausel kann in der CREATE PROCEDURE-Anweisung angegeben werden, damit das Annehmen der Identität eines anderen Benutzers unterstützt wird, oder um Benutzern bzw. Anwendungen das Ausführen bestimmter Datenbankaktivitäten zu ermöglichen, ohne dass sie über direkte Berechtigungen für die zugrunde liegenden Objekte und Befehle verfügen.The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. Einige Aktionen, wie beispielsweise TRUNCATE TABLE, besitzen keine erteilbaren Berechtigungen.For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. Um TRUNCATE TABLE auszuführen, benötigt der Benutzer für die festgelegte Tabelle die ALTER-Berechtigungen.To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Die Erteilung der ALTER-Berechtigungen für eine Tabelle kann sich als nicht ideal erweisen, weil der Benutzer dann Berechtigungen besitzt, die über die Möglichkeit hinausgehen, die Tabelle abzuschneiden.Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. Durch die Aufnahme der TRUNCATE TABLE-Anweisung in ein Modul und durch die Festlegung, dass das Modul 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 das Modul erteilen.By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

Wenn eine Prozedur über das Netzwerk aufgerufen wird, ist nur der Aufruf zum Ausführen der Prozedur sichtbar.When calling a procedure over the network, only the call to execute the procedure is visible. Daher haben böswillige Benutzer keine Möglichkeit, die Namen von Tabellen- und Datenbankobjekten einzusehen, eigene Transact-SQLTransact-SQL -Anweisungen einzubetten oder wichtige Daten zu suchen.Therefore, malicious users cannot see table and database object names, embed Transact-SQLTransact-SQL statements of their own, or search for critical data.

Die Verwendung von Prozedurparametern bietet Schutz vor Angriffen durch Einschleusung von SQL-Befehlen.Using procedure parameters helps guard against SQL injection attacks. Da die Parametereingabe als Literalwert und nicht als ausführbarer Code behandelt wird, ist es für einen Angreifer schwieriger, einen Befehl in die Transact-SQLTransact-SQL -Anweisung(en) innerhalb der Prozedur einzufügen und die Sicherheit zu gefährden.Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQLTransact-SQL statement(s) inside the procedure and compromise security.

Prozeduren können verschlüsselt werden und helfen, den Quellcode zu verbergen.Procedures can be encrypted, helping to obfuscate the source code. Weitere Informationen finden Sie unter SQL Server Encryption.For more information, see SQL Server Encryption.

Wiederverwendung von CodeReuse of code
Code für wiederholte Datenbankvorgänge ist besonders für die Kapselung in Prozeduren geeignet.The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. Dies hat folgende Vorteile: Derselbe Code muss nicht ständig neu geschrieben werden, Codeinkonsistenzen werden verringert, und alle Benutzer oder Anwendungen, die über die notwendigen Berechtigungen verfügen, können auf den Code zugreifen und diesen ausführen.This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

Einfachere WartungEasier maintenance
Wenn Clientanwendungen Prozeduren aufrufen und Datenbankvorgänge auf die Datenebene beschränkt bleiben, müssen bei Änderungen in der zugrunde liegenden Datenbank nur die Prozeduren aktualisiert werden.When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. Die Anwendungsebene bleibt von der Datenebene getrennt und ist von Änderungen an Datenbanklayouts, Beziehungen oder Prozessen nicht betroffen.The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

Verbesserte LeistungImproved performance
Eine Prozedur wird standardmäßig bei der ersten Ausführung kompiliert. Gleichzeitig wird ein Ausführungsplan erstellt, der für nachfolgende Ausführungen wiederverwendet wird.By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Da vom Abfrageprozessor kein neuer Plan erstellt werden muss, wird für die Verarbeitung der Prozedur normalerweise weniger Zeit benötigt.Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

Nachdem an den Tabellen oder Daten, auf die die Prozedur verweist, umfangreichere Änderungen vorgenommen wurden, wird die Prozedur aufgrund des vorkompilierten Plans möglicherweise langsamer ausgeführt.If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In diesem Fall kann die Leistung durch eine Neukompilierung der Prozedur und eine erzwungene Neuerstellung des Ausführungsplans verbessert werden.In this case, recompiling the procedure and forcing a new execution plan can improve performance.

Typen von gespeicherten ProzedurenTypes of Stored Procedures

Benutzerdefinierte DateigruppeUser-defined
Eine benutzerdefinierte Prozedur kann in einer benutzerdefinierten Datenbank sowie in allen Systemdatenbanken außer der Ressourcendatenbank erstellt werden.A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. Die Prozedur kann entweder in Transact-SQLTransact-SQL oder als Verweis auf eine MicrosoftMicrosoft .NET Framework.NET Framework CLR-Methode (Common Language Runtime) entwickelt werden.The procedure can be developed in either Transact-SQLTransact-SQL or as a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method.

Temporäre ProzedurenTemporary
Temporäre Prozeduren stellen eine Art benutzerdefinierter Prozedur dar.Temporary procedures are a form of user-defined procedures. Temporäre Prozeduren verhalten sich wie dauerhafte Prozeduren, mit der Ausnahme, dass temporäre Prozeduren in tempdbgespeichert werden.The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb. Es gibt zwei Arten von temporären Prozeduren: lokale und globale temporäre Prozeduren.There are two types of temporary procedures: local and global. Sie unterscheiden sich hinsichtlich ihrer Namen, ihrer Sichtbarkeit und ihrer Verfügbarkeit.They differ from each other in their names, their visibility, and their availability. Die Namen lokaler temporärer Prozeduren beginnen mit einem einzelnen Nummernzeichen (#). Sie sind nur im Rahmen der aktuellen Benutzerverbindung sichtbar und werden gelöscht, sobald die Verbindung getrennt wird.Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed. Die Namen globaler temporärer Prozeduren beginnen mit zwei Nummernzeichen (##). Nachdem sie erstellt wurden, sind sie für jeden Benutzer sichtbar und werden am Ende der letzten Sitzung, in der die Prozedur verwendet wird, gelöscht.Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

SystemSystem
Systemprozeduren sind in SQL ServerSQL Serverenthalten.System procedures are included with SQL ServerSQL Server. Sie werden physisch in der internen, ausgeblendeten Ressourcendatenbank gespeichert und logisch im sys -Schema jeder system- und benutzerdefinierten Datenbank angezeigt.They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. Außerdem verfügt die msdb-Datenbank über gespeicherte Systemprozeduren im dbo -Schema, die zum Planen von Warnungen und Aufträgen verwendet werden.In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Da Systemprozeduren mit dem Präfix sp_ beginnen, wird davon abgeraten, dieses Präfix beim Benennen benutzerdefinierter Prozeduren zu verwenden.Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. Eine vollständige Liste der systemgespeicherten Prozeduren finden Sie unter Systemgespeicherte Prozeduren (Transact-SQL).For a complete list of system procedures, see System Stored Procedures (Transact-SQL)

SQL ServerSQL Server unterstützt die Systemprozeduren, die eine Schnittstelle von SQL ServerSQL Server zu externen Programmen für verschiedene Wartungsaktivitäten bereitstellen. supports the system procedures that provide an interface from SQL ServerSQL Server to external programs for various maintenance activities. Diese erweiterten Prozeduren verwenden das Präfix xp_.These extended procedures use the xp_ prefix. Eine vollständige Liste der erweiterten Prozeduren finden Sie unter Gespeicherte allgemeine erweiterte Prozeduren (Transact-SQL).For a complete list of extended procedures, see General Extended Stored Procedures (Transact-SQL).

Erweiterte benutzerdefinierte ProzedurenExtended User-Defined
Mit erweiterten gespeicherten Prozeduren können externe Routinen in einer Programmiersprache wie C erstellt werden. Diese Prozeduren sind DLLs, die von einer Instanz von SQL ServerSQL Server dynamisch geladen und ausgeführt werden können.Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL ServerSQL Server can dynamically load and run.

Hinweis

Erweiterte gespeicherte Prozeduren werden in einer zukünftigen Version von SQL ServerSQL Serverentfernt.Extended stored procedures will be removed in a future version of SQL ServerSQL Server. Verwenden Sie dieses Feature nicht in einer neuen Entwicklungsarbeit, und ändern Sie Anwendungen, die dieses Feature verwenden, so schnell wie möglich.Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Erstellen Sie stattdessen CLR-Prozeduren.Create CLR procedures instead. Diese Methode bietet eine robustere und sicherere Alternative zum Schreiben von erweiterten Prozeduren.This method provides a more robust and secure alternative to writing extended procedures.

TaskbeschreibungTask Description ThemaTopic
Beschreibt, wie eine gespeicherte Prozedur erstellt wird.Describes how to create a stored procedure. Erstellen einer gespeicherten ProzedurCreate a Stored Procedure
Beschreibt, wie eine gespeicherte Prozedur geändert wird.Describes how to modify a stored procedure. Ändern einer gespeicherten ProzedurModify a Stored Procedure
Beschreibt, wie eine gespeicherte Prozedur gelöscht wird.Describes how to delete a stored procedure. Löschen einer gespeicherten ProzedurDelete a Stored Procedure
Beschreibt, wie eine gespeicherte Prozedur ausgeführt wird.Describes how to execute a stored procedure. Ausführen einer gespeicherten ProzedurExecute a Stored Procedure
Beschreibt, wie Berechtigungen für eine gespeicherte Prozedur erteilt werden.Describes how to grant permissions on a stored procedure. Erteilen von Berechtigungen für eine gespeicherte ProzedurGrant Permissions on a Stored Procedure
Beschreibt, wie Daten von einer gespeicherten Prozedur an eine Anwendung zurückgegeben werden.Describes how to return data from a stored procedure to an application. Zurückgeben von Daten von einer gespeicherten ProzedurReturn Data from a Stored Procedure
Beschreibt, wie eine gespeicherte Prozedur neu kompiliert wird.Describes how to recompile a stored procedure. Erneutes Kompilieren einer gespeicherten ProzedurRecompile a Stored Procedure
Beschreibt, wie eine gespeicherte Prozedur umbenannt wird.Describes how to rename a stored procedure. Umbenennen einer gespeicherten ProzedurRename a Stored Procedure
Beschreibt, wie die Definition einer gespeicherten Prozedur angezeigt wird.Describes how to view the definition of a stored procedure. Anzeigen der Definition einer gespeicherten ProzedurView the Definition of a Stored Procedure
Beschreibt, wie die Abhängigkeiten von einer gespeicherten Prozedur angezeigt werden.Describes how to view the dependencies on a stored procedure. Anzeigen der Abhängigkeiten einer gespeicherten ProzedurView the Dependencies of a Stored Procedure
Beschreibt, wie Parameter in einer gespeicherten Prozedur verwendet werden.Describes how Parameters are used in a stored procedure. ParameterParameters

CLR-gespeicherte ProzedurenCLR Stored Procedures