Aan de slag met opgeslagen Procedures in SQL Server
door: Greg Robidoux | bijgewerkt: 2020-07-30 | Comments (15) | verwant: meer > opgeslagen Procedures
probleem
Ik gebruik SQL Server al enige tijd, maar alle code die wordt uitgegeven tegen de database is ingebed in de toepassingscode. Ik weet dat je kunt createstored procedures, maar ik ben niet precies zeker waar te beginnen OF WAT IK nodig heb om Doto implementeren opgeslagen procedures.,
oplossing
opgeslagen procedures zijn niets meer dan een batch van T-SQL statements die in de database staan. In plaats van dat u meerdere statements uit uw toepassing moet geven, kunt u één commando geven om de opgeslagen procedure aan te roepen om abatch van het werk te doen in plaats van slechts één statement. Bovendien, omdat de code isstored in de database kunt u dezelfde set code opnieuw en opnieuw uit te geven zelfs uit verschillende toepassingen of een query venster. Om te beginnen, de rest van deze tip kijkt naar een aantal voorbeelden opgeslagen procedures en hoe u kunt beginnen en voort te bouwen op hen.,
onderstaande voorbeelden laten zien hoe eenvoudig het is om opgeslagen procedures aan te maken. Al deze voorbeelden maken gebruik van de dventureworks database, maar de voorbeelden moeten vrij duidelijk zijn dat je deze concepten kunt toepassen op je eigen databases en applicaties.
Voorbeeld 1 – eenvoudige opgeslagen procedure
dit eerste voorbeeld maakt een eenvoudige opgeslagen procedure die de top 1 record krijgt van de persoon.Contacttafel.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
nadat het bovenstaande is aangemaakt, gebruik het onderstaande commando om deze storedprocedure uit te voeren.,
EXEC uspGetContact
Dit zijn de resultaten van deze eerste query.
Voorbeeld 2 – opgeslagen procedure met een parameter
Dit volgende voorbeeld is een wijziging van het eerste voorbeeld, maar deze keer voegt een parameter toe die wordt doorgegeven aan de procedure om dynamisch de records te selecteren.In plaats van de procedure te maken gebruiken we de procedure te wijzigen om de procedure te wijzigen die we in Voorbeeld 1 hebben gemaakt in plaats van het eerst te laten vallen en het vervolgens opnieuw te maken.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
hieronder toont twee verschillende manieren waarop de opgeslagen procedure kan worden uitgevoerd. Het eerste voorbeeld geeft gewoon de parameterwaarde door die we willen gebruiken en het tweede voorbeeld bevat ook de parameternaam samen met de waarde. U kunt de opgeslagen procedure uitvoeren met een van deze commando ‘ s.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Dit zijn de resultaten van deze eerste query.,
Voorbeeld 3 – opgeslagen procedure met een parameter en uitvoerparameter
In dit voorbeeld hebben we zowel een invoerparameter als een uitvoerparameter.De output parameter zal gebruikt worden om het ContactID terug te geven dat we opzoeken in de opgeslagen procedure. Deze uitvoerparameter zal dan worden gebruikt om Depersonen ContactID, voornaam en achternaam te selecteren samen met eventuele adresrecords voor deze persoon.,
opnieuw veranderen we de opgeslagen procedure uspGetContact en vervolgens draaien we de volgende set code die procedure uspgetcontact uitvoert en dan gebaseerd op de retourwaarde die het krijgt zal het ook vragen naar de naam en adresinfo van de personen.
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
nadat de opgeslagen procedure is gewijzigd, voert u het onderstaande code-blok uit. Dit zal de hierboven opgeslagen procedure uitvoeren en als het ContactID een waarde heeft, zal het ook de persoon en adresgegevens retourneren.
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
Dit zijn de resultaten.,
Voorbeeld 4 – opgeslagen procedure met behulp van de raiserror statement
In dit voorbeeld combineren we de twee stappen in Voorbeeld 3 in één opgeslagen procedure.De eerste stap is om het ContactID en dan het tweede deel van de procedurezal opzoeken van de personen naam en adres info. We hebben ook in code toegevoegd om de aiserror statement te gebruiken om een fout te retourneren als er geen records worden gevonden.
Dit wordt dan tweemaal uitgevoerd om te laten zien hoe het eruit ziet wanneer gegevens worden gevonden en wanneer er geen gegevens worden gevonden., De raiserror statement kan worden gebruikt om te controleren hoe uw aanvraag geen gegevens of andere fouten verwerkt die kunnen optreden.
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"
Dit zijn de resultaten.
EXEC uspGetContact @LastName="Job"
Dit zijn de resultaten wanneer geen gegevens worden gevonden.
Voorbeeld 5 – opgeslagen procedure met een aparte aanroep opgeslagen procedure
Hier is een ander voorbeeld waar we twee opgeslagen procedures hebben., De eerste storedprocedure uspfindcontact lookups de eerste record dat een adres record heeft enthen retourneert de ContactID naar de oproep opgeslagen procedure om de persoon en adres info weer te geven.
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
de onderstaande code wijzigt de opgeslagen uspgetcontact-procedure die suspendfindcontact aanroept en de recordsets retourneert.
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"
Dit zijn de resultaten.
EXEC uspGetContact @LastName="Job"
Dit zijn de resultaten.,
Voorbeeld 6 – opgeslagen procedure met opmerkingen
Dit laatste voorbeeld neemt de uspgetcontact opgeslagen procedure en voegt commentaar toe aan de code, zodat u kunt zien hoe opmerkingen werken binnen een opgeslagen procedure.
opmerkingen kunnen op twee manieren worden gemaakt
- Met —
- met / * om het commentaarblok te beginnen en * / om het commentaarblok te beëindigen.
anders dan dat er niets anders is veranderd in de opgeslagen procedure.,
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
Dit zijn vrij eenvoudige voorbeelden, maar hopelijk geeft dit u een idee van hoe eenvoudig het is om opgeslagen procedures voor SQL Server aan te maken. Als u een SELECT statement kunt uitvoeren vanuit een query-venster of vanuit uw applicatie kunt u net zo gemakkelijk een astred-procedure uitvoeren als hierboven.,
volgende stappen
- Als u nog geen opgeslagen procedures gebruikt, geeft dit u hopelijk enig inzicht in wat u moet doen om ze te gaan gebruiken
- zoals vermeld zijn dit vrij eenvoudige voorbeelden, maar bijna alles wat u kunt doen met een partij van statements kan worden gecombineerd in een opgeslagen procedure en vervolgens steeds opnieuw worden gebruikt voor uw toepassingen.,
- bekijk thestored procedure tutorial voor meer voorbeelden
Laatst Bijgewerkt: 2020-07-30
Over de auteur
Greg Robidoux is de President van Edgewood Oplossingen en een mede-oprichter van MSSQLTips.com.Bekijk al mijn tips
- Meer Database Ontwikkelaar Tips…