Kom godt i gang med de Procedurer, der er Gemt i SQL Server
Af: Greg Robidoux | Opdateret: 2020-07-30 | Kommentarer (15) | Relateret: Mere > Lagrede Procedurer
Problem
jeg har været ved hjælp af SQL Server i et stykke tid, men alle af den kode, der er issuedagainst databasen er indlejret i ansøgningen kode. Jeg ved, at du kan oprettestored procedurer, men jeg er ikke helt sikker på, hvor du skal starte eller hvad jeg skal gøreat gennemføre lagrede procedurer.,
løsning
lagrede procedurer er intet mere end et parti af T-s .l-sætninger, der erlagret i databasen. I stedet for at skulle udstede flere udsagn fradin ansøgning kan du udstede en kommando for at ringe til den lagrede procedure for at gøre abatch af arbejde i stedet for kun en erklæring. Desuden, da koden eropbevares i databasen, kan du udstede det samme sæt kode igen og igenselv fra forskellige applikationer eller et forespørgselsvindue. For at komme i gang ser resten af dette tip på nogleprøve lagrede procedurer, og hvordan du kan komme i gang og bygge videre på dem.,nedenstående eksempler viser dig, hvor nemt det er at oprette lagrede procedurer. Allof disse eksempler bruger theadventure .orks database, men disse eksempler skal være ret ligetil, at du kan anvende disse begreber til dine egne databaser og applikationer.
eksempel 1 – simpel lagret procedure
dette første eksempel skaber en simpel lagret procedure, der får TOP 1 recordfra personen.Kontakt tabel.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
når ovenstående er oprettet, skal du bruge kommandoen nedenfor til at udføre denne storedprocedure.,
EXEC uspGetContact
Dette er resultaterne fra denne første forespørgsel.
Eksempel 2 – lagret procedure med en parameter
Dette næste eksempel er en modifikation af det første eksempel, men denne gang tilføjeen parameter, der er gået ind i proceduren til dynamisk at vælge de poster.I stedet for at bruge Opret PROCEDURE bruger vi ALTER PROCEDURE til at ændre den procedure, som vi oprettede I eksempel 1 i stedet for at droppe den først og derefter genskabe den.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
nedenfor viser to forskellige måder, hvorpå den lagrede procedure kan køres. Det første eksempel passerer bare den parameterværdi, vi vil bruge, og det andet eksempel inkluderer også parameternavnet sammen med værdien. Du kan også køre den gemte procedureen af disse kommandoer.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Dette er resultaterne fra denne første forespørgsel.,
Eksempel 3 – lagret procedure med en parameter og output parameter
I dette eksempel har vi både en input parameter, samt en OUTPUT parameter.Outputparameteren bruges til at videregive den kontakttid, som vi kigger på i den lagrede procedure. Denne outputparameter bruges derefter til at vælge personer kontakttid, fornavn og efternavn sammen med eventuelle adresseposter for thisperson.,
vi Igen er at ændre den lagrede procedure uspGetContact og derefter det andet weare kører det næste sæt af kode, der udfører proceduren uspGetContact og thenbased på den værdi, der returneres, det bliver det vil også søge for de 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
efter den lagrede procedure er blevet ændret Kør nedenstående blok kode. Dette vil udføre den ovenfor lagrede procedure, og hvis kontakttiden har en værdi, vil den også returnere personen og adresseoplysningerne.
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 resultaterne.,
Eksempel 4 – lagret procedure ved hjælp af RAISERROR erklæring
I dette eksempel vi er specialister i at kombinere de to trin i Eksempel 3 i en gemt procedure.Det første skridt er at få kontakttid og derefter den anden del af procedurenvil opsøge personernes navn og adresse info. Vi har også tilføjet i kode for at bruge theRAISERROR erklæring til at returnere en fejl, hvis der ikke findes nogen poster.
dette køres derefter to gange for at vise, hvordan det ser ud, når data findes, og når der ikke findes nogen data., RAISERROR-sætningen kan bruges til at kontrollere, hvordan din applicationhandles Ingen data eller anden fejl, der måtte opstå.
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 resultatet.
EXEC uspGetContact @LastName="Job"
Dette er resultaterne, når der ikke findes nogen data.
Eksempel 5 – lagret procedure med en separat ringer, der er gemt procedure
Her er et andet eksempel, hvor vi har to stored procedures., Den første lagrede procedure uspFindContact-opslag den første post, der har en adressepost, returnerer derefter ContactID til den opkaldende lagrede procedure for igen at vise personen og adresseinfo.
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 ændrer den uspgetcontact-lagrede procedure, som callsuspFindContact og returnerer postsættene.
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 resultatet.
EXEC uspGetContact @LastName="Job"
Dette er resultatet.,
Eksempel 6 – lagret procedure med kommentarer
Det sidste eksempel tager uspGetContact gemt procedure og tilføjer commentsto koden, så du kan se, hvordan kommentarer arbejde inden for en gemt procedure.
kommentarer kan laves på to måder
- ved hjælp af —
- ved hjælp af / * for at starte kommentarblokken og * / for at afslutte kommentarblokken.
andet end at intet andet har ændret sig i den lagrede 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
Dette er ret enkle eksempler, men forhåbentlig giver dette dig en ID.om, hvor nemt det er at oprette lagrede procedurer for s .l Server. Hvis du kan køre en SELECT statementfrom enten en forespørgsel vindue eller fra din ansøgning kan du lige så nemt køre astored procedure som vist ovenfor.,
Næste Skridt
- Hvis du ikke allerede bruger lagrede procedurer, der forhåbentlig giver yousome indsigt i, hvad du skal gøre for at begynde at bruge dem
- Som nævnt disse er temmelig enkle eksempler, men bare om noget, du kan gøre med et parti af erklæringer kan kombineres til en lagret procedure, andthen brugt igen og igen over for dine applikationer.,
- Tjek thestored procedure tutorial for flere eksempler
Senest Opdateret: 2020-07-30
Om forfatteren
Greg Robidoux er Formand for Edgewood Løsninger og medstifter af MSSQLTips.com.Se alle mine tips
- Mere Database Udvikler Tips…