Komma igång med Lagrade Procedurer i SQL Server
Av: Greg Robidoux | Uppdaterad: 2020-07-30 | Kommentarer (15) | Relaterat: Mer > Lagrade Procedurer
Problem
jag har varit med SQL Server under en tid, men all kod som är issuedagainst databasen är inbäddade i programkoden. Jag vet att du kan skapalagrade procedurer, men jag är inte exakt säker på var du ska börja eller vad jag behöver göratt genomföra lagrade procedurer.,
lösning
lagrade procedurer är inget annat än ett parti T-SQL-satser som lagras i databasen. Istället för att behöva utfärda flera uttalanden fråndin applikation kan du utfärda ett kommando för att ringa den lagrade proceduren för att göra abatch av arbete istället för bara ett uttalande. Dessutom, eftersom koden ärlagrad i databasen kan du utfärda samma uppsättning kod om och om igenäven från olika applikationer eller ett frågefönster. För att komma igång tittar resten av detta tips på någraprov lagrade procedurer och hur du kan komma igång och bygga vidare på dem.,
nedanstående exempel visar hur enkelt det är att skapa lagrade procedurer. Allof dessa exempel använder theAdventureWorks databasen, men theseexamples borde vara ganska straightforwardthat du kan tillämpa dessa begrepp till dina egna databaser och applikationer.
exempel 1 – enkel lagrad procedur
det här första exemplet skapar en enkel lagrad procedur som får topp 1-postfrån personen.Kontaktbordet.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
Efter att ovanstående har skapats använd kommandot nedan för att utföra detta lagratförfarande.,
EXEC uspGetContact
detta är resultatet från den första frågan.
exempel 2 – lagrad procedur med en parameter
nästa exempel är en ändring av det första exemplet, men den här gången lägger du tillen parameter som skickas in i proceduren för att dynamiskt välja posterna.I stället för att använda CREATE-proceduren använder vi ALTER-proceduren för att ändra procedurenatt vi skapade i Exempel 1 istället för att släppa den först och sedan återskapa den.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
nedan visas två olika sätt som den lagrade proceduren kan köras på. Det första exempeletpasserar bara parametervärdet vi vill använda och det andra exemplet inkluderar ocksåparameternamnet tillsammans med värdet. Du kan köra den lagrade proceduren med antingenen av dessa kommandon.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
detta är resultatet från den första frågan.,
exempel 3 – lagrad procedur med en parameter och utgångsparameter
i det här exemplet har vi både en ingångsparameter och en UTGÅNGSPARAMETER.Utdataparametern kommer att användas för att skicka tillbaka Kontaktiden som vi söker upp i den lagrade proceduren. Denna utgång parameter kommer sedan att användas för att välja thepersons ContactID, Förnamn och Efternamn tillsammans med någon adress register för thisperson.,
igen vi ändrar den lagrade proceduren uspGetContact och sedan för det andra Weare kör nästa uppsättning kod som utför proceduren uspGetContact och thenbased på returvärdet det blir det kommer också att fråga efter personernas namn och Adressinfo.
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
efter det att den lagrade proceduren har ändrats kör nedanstående kodblock. Dettakommer att utföra ovanstående lagrade procedur och om Kontaktiden har ett värde kommer det också att returnera personen och adressinformationen.
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
detta är resultatet.,
exempel 4 – lagrad procedur med hjälp av RAISERROR-satsen
i det här exemplet kombinerar vi de två stegen i Exempel 3 till en lagrad procedur.Det första steget är att få Kontaktiden och sedan den andra delen av förfarandetkommer att leta upp personernas namn och adressinformation. Vi lade också till i kod för att använda theraiserror-satsen för att returnera ett fel om inga poster hittas.
detta körs sedan två gånger för att visa hur det ser ut när data hittas och när Inga data hittas., RAISERROR-satsen kan användas för att styra hur din applikationhanterar Inga data eller något annat fel som kan uppstå.
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"
detta är resultatet.
EXEC uspGetContact @LastName="Job"
detta är resultatet när Inga data hittas.
exempel 5 – lagrad procedur med en separat anropande lagrad procedur
Här är ett annat exempel där vi har två lagrade procedurer., Den första storedprocedure uspfindcontact söker den första posten som har en adresspost och returnerar sedan Kontaktiden till den anropande lagrade proceduren för att återigen visa personen och adressinformationen.
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
koden nedan ändrar den lagrade proceduren för uspgetcontact som callsuspFindContact och returnerar postuppsättningarna.
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"
detta är resultatet.
EXEC uspGetContact @LastName="Job"
detta är resultatet.,
exempel 6 – lagrad procedur med kommentarer
det här sista exemplet tar uspgetcontact lagrad procedur och lägger till kommentarer till koden så att du kan se hur kommentarer fungerar inom en lagrad procedur.
kommentarer kan göras på två sätt
- använda —
- använda/* för att starta kommentarblocket och */ för att avsluta kommentarblocket.
annat än att inget annat har ändrats i den lagrade proceduren.,
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
det här är ganska enkla exempel, men förhoppningsvis ger det dig en uppfattning om hur lätt det är att skapa lagrade procedurer för SQL Server. Om du kan köra en utvald uttalandefrån antingen ett frågefönster eller från ditt program kan du lika enkelt köra häpnadsväckande procedur som show ovan.,
nästa steg
- Om du inte redan använder lagrade procedurer förhoppningsvis ger detta dignågon insikt om vad du behöver göra för att börja använda dem
- som nämnts är dessa ganska enkla exempel, men nästan allt du kan göra med en sats uttalanden kan kombineras till en lagrad procedur ochsedan används om och om igen för dina program.,
- kolla in thestored procedure tutorial för fler exempel
Senast uppdaterad: 2020-07-30
om författaren
Greg Robidoux är VD för Edgewood Solutions och en av grundarna av MSSQLTips.com. Visa alla mina tips
- fler Databasutvecklartips…