Erste Schritte mit gespeicherten Prozeduren in SQL Server
Von: Greg Robidoux / Aktualisiert: 2020-07-30 / Kommentare (15) | Verwandte: Mehr > Gespeicherte Prozeduren
Problem
Ich benutze SQL Server seit einiger Zeit, aber der gesamte Code, der ausgegeben wirdgegen die Datenbank in den Anwendungscode eingebettet ist. Ich weiß, dass Sie gespiegelte Prozeduren erstellen können, bin mir jedoch nicht genau sicher, wo ich anfangen soll oder was ich zum Implementieren gespeicherter Prozeduren benötige.,
Lösung
Gespeicherte Prozeduren sind nichts anderes als ein Stapel von T-SQL-Anweisungen, die in der Datenbank verankert sind. Anstatt mehrere Anweisungen aus Ihrer Anwendung ausgeben zu müssen, können Sie einen Befehl ausgeben, um die gespeicherte Prozedur aufzurufen, um einen Arbeitsabbruch durchzuführen, anstatt nur eine Anweisung. Da der Code in der Datenbank gespeichert ist, können Sie denselben Codesatz auch immer wieder aus verschiedenen Anwendungen oder einem Abfragefenster ausgeben. Um loszulegen, der Rest dieses Tipp schaut auf somesample gespeicherte Prozeduren und wie Sie beginnen können und bauen auf sie.,
Die folgenden Beispiele zeigen Ihnen, wie einfach es ist, gespeicherte Prozeduren zu erstellen. Allof Diese Beispiele verwenden dieAdventureWorks-Datenbank, aber diese Beispiele sollten ziemlich einfach sein, dass Sie diese Konzepte auf Ihre eigenen Datenbanken und Anwendungen anwenden können.
Beispiel 1-einfache gespeicherte Prozedur
Dieses erste Beispiel erstellt eine einfache gespeicherte Prozedur, die den obersten 1-Datensatz abruftvon der Person.Kontakt Tabelle.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
Nachdem das Obige erstellt wurde, verwenden Sie den folgenden Befehl, um dieses storedprocedure auszuführen.,
EXEC uspGetContact
Dies sind die Ergebnisse dieser ersten Abfrage.
Beispiel 2 – gespeicherte Prozedur mit einem Parameter
Dieses nächste Beispiel ist eine Änderung des ersten Beispiels, aber dieses Mal fügen Sie einen Parameter hinzu, der an die Prozedur übergeben wird, um die Datensätze dynamisch auszuwählen.Anstatt CREATE PROCEDURE verwenden wir ALTER PROCEDURE, um das procedurethat, das wir in Beispiel 1 erstellt haben, zu ändern, anstatt es zuerst zu löschen und dann neu zu erstellen.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
Unten sehen Sie zwei verschiedene Möglichkeiten, die gespeicherte Prozedur ausgeführt werden kann. Das erste examplejust übergibt den Parameterwert, den wir verwenden möchten, und das zweite Beispiel beinhaltetder Parametername zusammen mit dem Wert. Sie können die gespeicherte Prozedur mit eitherone dieser Befehle ausführen.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Dies sind die Ergebnisse dieser ersten Abfrage.,
Beispiel 3 – gespeicherte Prozedur mit einem Parameter und Ausgabeparameter
In diesem Beispiel haben wir sowohl einen Eingabeparameter als auch einen Ausgabeparameter.Der Ausgabeparameter wird verwendet, um die ContactID zurückzugeben, die wir in der gespeicherten Prozedur suchen. Dieser Ausgabeparameter wird dann verwendet, um diepersons ContactID, Vorname und Nachname zusammen mit allen Adressdatensätzen für thisperson auszuwählen.,
Wieder ändern wir die gespeicherte Prozedur uspGetContact und dann zweitens führen wir den nächsten Satz von Code aus, der die Prozedur uspGetContact ausführt, und thenbasierend auf dem Rückgabewert, den es erhält, fragt es auch nach dem Namen der Personen und addressinfo.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
Nachdem die gespeicherte Prozedur geändert wurde, führen Sie den folgenden Codeblock aus. Thiswill führen Sie die obige Prozedur, und wenn die ContactID einen Wert hat, der es willalso Rückkehr der person und die Adresse info.
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
Dies ist das Ergebnis.,
Beispiel 4 – gespeicherte Prozedur mit der RAISERROR-Anweisung
In diesem Beispiel kombinieren wir die beiden Schritte in Beispiel 3 in eine gespeicherte Prozedur.Der erste Schritt besteht darin, die ContactID abzurufen, und dann wird der zweite Teil des Verfahrens den Namen und die Adressinformationen der Personen nachschlagen. Wir haben auch Code hinzugefügt, um die Anweisung theRAISERROR zu verwenden, um einen Fehler zurückzugeben, wenn keine Datensätze gefunden werden.
Dies wird dann zweimal ausgeführt, um zu zeigen, wie es aussieht, wenn Daten gefunden werden undwenn keine Daten gefunden werden., Die RAISERROR Anweisung kann verwendet werden, um zu steuern, wie Ihre applicationhandles keine Daten oder andere Fehler, die auftreten können.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Dies ist das Ergebnis.
EXEC uspGetContact @LastName="Job"
Dies ist das Ergebnis, wenn keine Daten gefunden werden.
Beispiel 5 – gespeicherte Prozedur mit einer separaten aufrufenden gespeicherten Prozedur
Hier ist ein weiteres Beispiel, in dem wir zwei gespeicherte Prozeduren haben., Der erste storedprocedure uspFindContact sucht den ersten Datensatz, der einen Adressdatensatz hat unddann gibt die ContactID an die aufrufende gespeicherte Prozedur zurück, um die Adresse und die Adressinformationen erneut anzuzeigen.
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
Der folgende Code ändert die gespeicherte Prozedur uspGetContact, die aufruftsuspfindcontact und gibt die Recordsets zurück.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Dies ist das Ergebnis.
EXEC uspGetContact @LastName="Job"
Dies ist das Ergebnis.,
Beispiel 6 – gespeicherte Prozedur mit Kommentaren
Dieses letzte Beispiel verwendet die gespeicherte uspGetContact-Prozedur und fügt dem Code Kommentare hinzu, damit Sie sehen können, wie Kommentare in einer gespeicherten Prozedur funktionieren.
Kommentare können auf zwei Arten gemacht werden
- mit —
- mit/* um den Kommentarblock zu beginnen und * / um den Kommentarblock zu beenden.
Ansonsten hat sich in der gespeicherten Prozedur nichts anderes geändert.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
Dies sind ziemlich einfache Beispiele, aber hoffentlich gibt Ihnen dies eine Vorstellung davon, wieasy es ist, gespeicherte Prozeduren für SQL Server zu erstellen. Wenn Sie eine SELECT statementfrom entweder aus einem Abfragefenster oder aus Ihrer Anwendung ausführen können, können Sie genauso einfach astored Prozedur wie oben gezeigt.,
Nächste Schritte
- Wenn Sie nicht bereits mithilfe von gespeicherten Prozeduren hoffentlich gibt yousome Einblick, was Sie tun müssen, um zu beginnen, Sie
- Wie erwähnt, dies sind ziemlich einfache Beispiele, aber gerade über alles youcan tun mit einer Gruppe von Aussagen können kombiniert werden in eine gespeicherte Prozedur dann verwendet werden über und über wieder für Ihre Anwendungen.,
- Check out thestored procedure tutorial for more examples
Last Updated: 2020-07-30
About the author
Greg Robidoux ist Präsident von Edgewood Solutions und Mitbegründer von MSSQLTips.com.Alle meine Tipps
- Weitere Tipps für Datenbankentwickler…