Gespeicherte Prozeduren in SQL | Ein Überblick

In diesem Blog besprechen wir gespeicherte Prozeduren in SQL, mit denen Sie eine Reihe von Code speichern und bei Bedarf immer wieder verwenden können. Gespeicherte Prozeduren ähneln Ansichten. Allerdings können Sie mit gespeicherten Prozeduren allgemeine Tabellenoperationen wie DROP, TRUNCATE, DELETE usw. ausführen, die mit Ansichten nicht möglich sind.

Gespeicherte Prozeduren sind außerdem vorkompiliert, sodass sie schneller als Ansichten ausgeführt werden. Außerdem wird die Menge der an den Datenbankserver gesendeten Daten minimiert.

Inhaltsverzeichnis

• Syntax für gespeicherte Prozeduren in SQL: Ohne Parameter
• Syntax für gespeicherte Prozeduren in SQL: Mit Parametern
• Beispielszenario gespeicherter Prozeduren in SSMS
• Zweites Beispielszenario
• Drittes Beispielszenario
• Speicherort gespeicherter gespeicherter Prozeduren in SQL
• Erstellen einer gespeicherten Prozedur in SQL mit Parametern
• Erstellen einer gespeicherten Prozedur mit einem Standardwert
• Abschluss

Syntax für gespeicherte Prozeduren in SQL: Ohne Parameter

Um eine gespeicherte Prozedur ohne Parameter zu erstellen, müssen Sie mit der CREATE- Funktion beginnen . Fügen Sie dann die PROC- Funktion hinzu, geben Sie direkt danach den Prozedurnamen ein und fügen Sie die AS- Funktion hinzu.

Das Erstellen einer gespeicherten Prozedur in ähnelt dem Erstellen von Tabellen und Ansichten. Der Unterschied liegt jedoch im Prozess der Datenbeschaffung.

Wenn wir beispielsweise Daten aus einer Ansicht abrufen möchten, verwenden wir „ SELECT * FROM view_name “. Für gespeicherte Prozeduren hingegen verwenden wir EXEC, was „ausführen“ bedeutet, und geben anschließend den Namen der gespeicherten Prozedur ein .

Sobald wir eine gespeicherte Prozedur ausführen , werden auch die Informationen basierend auf den von Ihnen hinzugefügten Anweisungen oder Befehlen angezeigt.

Syntax für gespeicherte Prozeduren in SQL: Mit Parametern

Kommen wir zum . Der Prozess ähnelt dem vorherigen Beispiel, das ich gezeigt habe. Der einzige Unterschied besteht darin, dass Sie die Parameter vor der AS- Funktion und auch den Datentyp direkt danach angeben müssen .

Sie können den Parameter, den Sie bei der Erstellung der gespeicherten Prozedur deklariert haben, auch in Ihren Befehlen verwenden. Dadurch sparen Sie Zeit bei der Eingabe mehrerer Werte. Anstatt beispielsweise customerID1 , customerID2 usw. manuell einzugeben, haben wir @custid oder den Parameter verwendet, um einen langen Codesatz zu vermeiden.

Bitte beachten Sie, dass es bei der Verwendung oder Einbindung eines Parameters wichtig ist, das @ -Symbol zu verwenden. Sie können auch beliebig viele Parameter angeben, nicht nur einen. So führen wir gespeicherte Prozeduren mit Parametern aus.

Wie Sie sehen, haben wir bei der Ausführung der gespeicherten Prozedur auch den Parameter mit einem Wert gleich 1 verwendet . Dadurch werden nur die Informationen von custid1 angezeigt .

Beispielszenario gespeicherter Prozeduren in SSMS

Sehen wir uns weitere Beispiele für gespeicherte Prozeduren in SQL an. Zunächst werde ich eine gespeicherte Beispielprozedur erstellen, indem ich den unten hervorgehobenen Befehl verwende.

Als Namenskonvention beim Erstellen einer gespeicherten Prozedur verwenden wir normalerweise „usp“ oder „sp“, um anzugeben, dass es sich um eine gespeicherte Prozedur handelt. Wenn Sie sich fragen, was „USP“ bedeutet: Es steht einfach für benutzerdefinierte gespeicherte Prozedur. 

Als Nächstes schreiben wir Abfragen für die gespeicherte Prozedur, die bei ihrer Ausführung verarbeitet werden sollen.

Die Abfragen im vorherigen Beispiel entfernen einfach die Tabelle mit dem Namen dbo.stageOrders . Anschließend wird dbo.stageOrders mit den Daten aus der Sales.SalesOrderHeader- Tabelle neu erstellt. 

Lassen Sie uns diese gespeicherte Prozedur erstellen , indem wir den folgenden Code markieren und auf die Schaltfläche „Ausführen“ klicken .

Danach sollten Sie eine Meldung wie diese sehen.

Dann führen wir nun die gespeicherte Prozedur aus, indem wir EXEC usp_TEST ausführen .

Nach der Ausführung der gespeicherten Prozedur usp_TEST sollte dbo.stageOrders nun über die Daten von Sales.SalesOrderHeader verfügen . Lassen Sie uns den Inhalt von dbo.stageOrders überprüfen , indem wir den folgenden Befehl ausführen.

Das Ergebnis sind die Daten, die wir in „dbo.stageOrders“ basierend auf den Befehlen erstellt haben, die wir unserer gespeicherten Prozedur hinzugefügt haben.

Zweites Beispielszenario

Kommen wir zu einem anderen Beispiel. Diesmal möchte ich nicht die Tabelle „dbo.stageOrders“ löschen und dieselbe Tabelle mit Daten aus der Tabelle „Sales.SalesOrderHeader“ neu erstellen .

Stattdessen möchte ich nur, dass die Daten aus der Tabelle „Sales.SalesOrderHeader“ immer dann angezeigt werden, wenn ich die gespeicherte Prozedur „ usp_TEST “ ausführe .

Um dies durchzuführen, muss ich mithilfe der ALTER- Anweisung Änderungen an der kürzlich erstellten gespeicherten Prozedur vornehmen.

In diesem Beispiel haben wir die ALTER- Anweisung verwendet, die zum Ändern unserer gespeicherten Prozedur verwendet wird. Sie können dies auch auf Ansichten und Tabellen anwenden. Sobald wir den Code im obigen Screenshot ausführen, sollte er die von unserer gespeicherten Prozedur zu verarbeitenden Abfragen ändern.

Dieses Mal wird keine Tabelle neu erstellt, wie wir es zuvor getan haben. Wenn wir „ usp_TEST “ ausführen , werden lediglich die Daten aus der Sales.SalesOrderHeader- Tabelle abgerufen .

Drittes Beispielszenario

Machen wir ein anderes Beispiel. Dieses Mal verwenden wir die Tabelle „dbo.stageOrders“ wieder, die wir in unserem ersten Beispiel zum Erstellen einer gespeicherten Prozedur in SSMS hatten . Wir beginnen mit diesem Beispiel, indem wir die Tabelle dbo.stageOrders auswählen. 

Die Ergebnisse nach Auswahl aller Daten aus dbo.stageOrders sollten wie folgt aussehen.

Jetzt leeren wir die Tabelle dbo.stageOrders, indem wir usp_TEST ausführen . Wir können das tun, indem wir den folgenden Befehl hinzufügen.

Nachdem wir den Befehl TRUNCATE TABLE hinzugefügt haben , müssen wir usp_TEST aktualisieren .

Danach sollte eine Meldung angezeigt werden , dass die Befehle erfolgreich abgeschlossen wurden . Dann führen wir usp_TEST erneut aus.

Beim Ausführen von usp_TEST werden alle Datensätze aus Sales.SalesOrderHeader angezeigt und auch die Tabelle dbo.stageOrders geleert . 

Um zu überprüfen, ob die Tabelle dbo.stageOrders leer ist, müssen wir sie mit dem folgenden Befehl auswählen und ausführen.

Wenn wir den obigen Code ausführen, sehen wir, dass die Tabelle dbo.stageOrders jetzt leer ist. Dies ist auf den TRUNCATE TABLE- Befehl zurückzuführen, den wir zum Aktualisieren von usp_TEST verwendet haben .

Speicherort gespeicherter gespeicherter Prozeduren in SQL

Wenn Sie sehen möchten, wo die gespeicherte Prozedur in SQL gespeichert wird, gehen Sie zum Bedienfeld „ Objekt-Explorer“ auf der linken Seite und klicken Sie auf das Symbol „ + “ vor der Datenbank, an der Sie arbeiten.

Klicken Sie dann mit der rechten Maustaste auf Programmierbarkeit und wählen Sie Aktualisieren .

Erweitern Sie den Ordner oder die Gruppe „Programmierbarkeit“ , indem Sie auf das Symbol „ + “ klicken. Erweitern Sie dann die Gruppe „Gespeicherte Prozeduren“, indem Sie denselben Schritt ausführen. Innerhalb der Gruppe „Gespeicherte Prozeduren“ sollten Sie dbo.usp_TEST sehen .

Wenn Sie überprüfen möchten, welche Befehle oder Abfragen eine bestimmte gespeicherte Prozedur in SQL ausführt, können Sie mit der rechten Maustaste auf eine gespeicherte Prozedur klicken und die Schritte im Screenshot unten ausführen.

Danach wird die gespeicherte Prozedur in einer anderen Registerkarte geöffnet, wo Sie die darin bereitgestellten Befehle sehen können. So sieht dbo.usp_TEST beim Öffnen aus.

Wie Sie sehen, gibt es vor der CREATE- Anweisung einige Standardbefehle . Sie können das einfach löschen, wenn Sie möchten. 

Jetzt wissen Sie, wie Sie überprüfen können, welche Befehle eine gespeicherte Prozedur in SQL ausführt.

Erstellen einer gespeicherten Prozedur in SQL mit Parametern

Als Nächstes erstellen wir eine gespeicherte Prozedur mit Parametern. Beispielsweise verwenden wir den folgenden Code, um eine neue gespeicherte Prozedur zu erstellen.

Im Beispielcode habe ich den gleichen Prozess zum Erstellen einer gespeicherten Prozedur namens usp_GetCustomer verwendet . Dann habe ich einen Parameter hinzugefügt, der @CustomerID mit dem Eingabetyp INT ist .

Beachten Sie, dass Sie nach dem Hinzufügen eines Parameters beim Erstellen einer gespeicherten Prozedur immer einen Parameter angeben sollten, wenn Sie einen Befehl ausführen.

Sehen wir uns an, was passiert, wenn wir usp_GetCustomer ausführen , ohne einen Parameter anzugeben.

Beim Ausführen von usp_GetCustomer ohne Parameter wurde eine Fehlermeldung angezeigt. So würde es aussehen, wenn wir usp_GetCustomer mit einem Parameter ausführen.

Mit einem bereitgestellten Parameter können wir bei der Ausführung unserer gespeicherten Prozedur ein korrektes Ergebnis erzielen .

Erstellen einer gespeicherten Prozedur mit einem Standardwert

Wenn Sie vermeiden möchten, dass beim Ausführen einer gespeicherten Prozedur mit einem Parameter eine Fehlermeldung angezeigt wird, können Sie einen Standardwert festlegen, der als Standardparameter dient.

Beispielsweise erstellen wir eine gespeicherte Prozedur mit dem Namen usp_GetOrdersByYear .

Dann füge ich einen Parameter @OrderYear mit dem Eingabetyp „ INT “ und einem Standardwert hinzu, der 2011 ist .

Wenn wir usp_GetOrdersByYear ohne Angabe eines Parameters ausführen, werden Datensätze mit dem Jahr 2011 angezeigt .

Wenn wir andererseits usp_GetOrdersByYear mit 2014 als gegebenem Parameter ausführen, sollten Datensätze mit dem Jahr 2014 angezeigt werden .

Auf diese Weise nutzen Sie gespeicherte Prozeduren bei Ihren täglichen Datenverwaltungsaufgaben.

HAVING-Klausel in SQL-Aggregatfunktionen
ISNULL SQL-Funktion in Case-Anweisungen
SQL-Datenextraktion mit OFFSET und FETCH

Abschluss

Wenn Sie alles berücksichtigen, haben Sie gelernt, was eine gespeicherte Prozedur in SQL ist und welchen Zweck sie hat. Wir haben auch die ALTER- Anweisung besprochen, die zum Vornehmen von Änderungen oder Aktualisierungen in einer aktuellen gespeicherten Prozedur verwendet wird.

Darüber hinaus haben Sie gelernt, dass es verschiedene Methoden zum Erstellen gespeicherter Prozeduren in SQL gibt, und erfahren, wie Sie Fehler beim Ausführen einer gespeicherten Prozedur vermeiden, indem Sie einen Standardwert angeben.

Am wichtigsten ist, dass Sie gelernt haben, gespeicherte Prozeduren zum Speichern von Befehlssätzen zu verwenden, um die wiederholte Ausführung langer Codesätze zu vermeiden. Als letzte Erinnerung: Vergessen Sie nicht, bei der Angabe eines Parameters das Symbol „ @ “ zu verwenden.

Alles Gute,

Hafiz