Komme i gang med Lagrede Prosedyrer i SQL Server
Av: Greg Robidoux | Oppdatert: 2020-07-30 | Kommentarer (15) | i Slekt: Mer > Lagrede Prosedyrer
Problem
jeg har vært ved hjelp av SQL Server for noen tid, men alle av koden som er issuedagainst databasen er innebygd i programmet kode. Jeg vet at du kan createstored prosedyrer, men jeg er ikke helt sikker på hvor du skal begynne eller hva jeg trenger å doto implementere lagrede prosedyrer.,
Løsning
Lagrede prosedyrer er ingenting mer som en batch av T-SQL-setninger som arestored i databasen. I stedet for å måtte problemer flere uttalelser fromyour programmet kan du utstede en kommando for å ringe en lagret prosedyre for å gjøre abatch arbeid i stedet for bare én uttalelse. I tillegg, siden koden isstored i databasen kan du sende ut samme sett av koden over og over againeven fra ulike programmer eller en spørring vinduet. For å komme i gang, resten av dette tipset ser på somesample lagrede prosedyrer, og hvordan du kan komme i gang og bygge videre på dem.,
eksemplene nedenfor for å vise deg hvor enkelt det er å opprette lagrede prosedyrer. Allof disse eksemplene bruker theAdventureWorks database, men theseexamples bør være ganske straightforwardthat du kan bruke disse begrepene til dine egne databaser og programmer.
Eksempel 1 – enkel lagret prosedyre
Dette er første eksempel oppretter en enkel lagret prosedyre som får TOPP 1 recordfrom Personen.Ta kontakt med bordet.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
Etter det ovenstående har blitt opprettet kan du bruke kommandoen nedenfor for å utføre denne storedprocedure.,
EXEC uspGetContact
Dette er resultatene fra denne første spørringen.
Eksempel 2 – lagret prosedyre med en parameter
Dette neste eksempel er en modifikasjon av det første eksemplet, men denne gangen addinga parameter som er gått inn i prosedyre for dynamisk å velge oppføringer.I stedet for å bruke SKAPE PROSEDYREN vi bruker ENDRE PROSEDYREN for å endre procedurethat vi opprettet i Eksempel 1 i stedet for å slippe det først, og deretter å gjenskape den.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
Nedenfor viser to ulike måter som er lagret prosedyre kan kjøres. Den første examplejust passerer parameter verdi vi ønsker å bruke, og det andre eksempelet også includesthe parameter navn sammen med verdien. Du kan kjøre den lagrede prosedyren med eitherone av disse kommandoene.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Dette er resultatene fra denne første spørringen.,
Eksempel 3 – lagret prosedyre med en parameter og utgang parameter
I dette eksempelet har vi både en input-parameter, så vel som en UTGANG parameter.Output parameter vil bli brukt til å gi tilbake ContactID at vi er lookingup i den lagrede prosedyren. Dette utgang parameter vil da bli brukt til å velge thepersons ContactID, Fornavn og Etternavn sammen med hvilken som helst adresse oppføringer for thisperson.,
Igjen, vi er endring av lagret prosedyre uspGetContact og deretter det andre weare kjører neste sett med kode som utfører prosedyren uspGetContact og thenbased på returverdien det blir det vil også spørre for personer, navn og 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
Etter den lagrede prosedyren har blitt endret kjøre nedenfor blokk med kode. Thiswill kjøre over en lagret prosedyre, og hvis ContactID har en verdi det willalso gå tilbake til den person og 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
Dette er resultatene.,
Eksempel 4 – lagret prosedyre ved hjelp av RAISERROR uttalelse
I dette eksempelet har vi kombinerer de to trinnene i Eksempel 3 i en lagret prosedyre.Det første trinnet er å få ContactID og deretter den andre delen av procedurewill oppslag personer, navn og adresse til info. Vi har også lagt til i koden for å bruke theRAISERROR erklæringen for å returnere en feil hvis ingen poster er funnet.
Dette er da som kjøres to ganger for å vise hvordan det ser ut når data er funnet andwhen ingen data funnet., Den RAISERROR uttalelse kan brukes til å kontrollere hvordan applicationhandles ingen data eller andre feil som kan oppstå.
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"
Dette er resultatene.
EXEC uspGetContact @LastName="Job"
Dette er resultatet når ingen data funnet.
Eksempel 5 – lagret prosedyre med en egen ringer til en lagret prosedyre
Her er et annet eksempel der vi har to lagrede prosedyrer., Den første storedprocedure uspFindContact oppslag på første posten som har en adresse posten andthen returnerer ContactID til å ringe en lagret prosedyre for å igjen vise theperson og adresse info.
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 nedenfor gjør et alter av uspGetContact lagret prosedyre som callsuspFindContact og returnerer postsett.
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"
Dette er resultatene.
EXEC uspGetContact @LastName="Job"
Dette er resultatene.,
Eksempel 6 – lagret prosedyre med kommentarer
Dette siste eksempelet tar uspGetContact lagret prosedyre, og legger til commentsto koden, slik at du kan se hvordan kommentarer arbeid innenfor en lagret prosedyre.
Kommentarer kan gjøres på to måter
- ved hjelp —
- ved hjelp av/* for å starte kommentar blokkere og */ for å avslutte kommentar blokk.
Andre enn noe annet som har endret seg i lagret prosedyre.,
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
Dette er ganske enkle eksempler, men forhåpentligvis gir dette deg en idé om howeasy det er å opprette lagrede prosedyrer for SQL Server. Hvis du kan kjøre en VELGER statementfrom enten en spørring vinduet eller fra programmet kan du like gjerne kjøre astored prosedyre som vist ovenfor.,
Neste Trinn
- Hvis du ikke allerede bruker lagrede prosedyrer som forhåpentligvis gir dette yousome innsikt i hva du trenger å gjøre for å begynne å bruke dem på
- Som nevnt, disse er ganske enkle eksempler, men bare om noe youcan gjøre med en gruppe av utsagn som kan kombineres til en lagret prosedyre andthen brukes om og om igjen for programmene dine.,
- Sjekk ut thestored prosedyre opplæringen for flere eksempler
Sist Oppdatert: 2020-07-30
Om forfatteren
Greg Robidoux er President i Edgewood Løsninger og en co-grunnlegger av MSSQLTips.com.Vis alle mine tips
- Mer-Database Utvikler Tips…