Efter du har læst denne artikel, vil du komme til at lære det grundlæggende i en dynamisk SQL, hvordan man opbygger sætninger baseret på variable værdier, og hvordan man kan udføre konstruerede sætninger ved hjælp af sp_executesql og UDFØRE() fra en lagret procedure.
Alle eksempler, der findes i denne lektion er baseret på Microsoft SQL Server Management Studio og prøve databaser fra AdventureWorks og WideWorldImporters. Du kan komme i gang med at bruge disse gratis værktøjer ved hjælp af min guide og komme i gang med at bruge s .l Server.,
Byg dynamisk s .l i en gemt procedure.
mange s .l, vi skriver, skrives eksplicit ind i den lagrede procedure. Dette er, hvad vi kalder den statiske s .l. Sådan hedder det, fordi det ikke ændrer sig. Når det er skrevet, betyder det, at det er sat hamret i sten.
nedenfor er et eksempel på en statisk s ?l:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
har du bemærket, at der er to udsagn her? Hver erklæring returnerer et resum.af jobtitler for et bestemt medarbejders fødselsår. Hvis vi ønsker at tilføje flere fødselsår, så skal vi tilføje flere udsagn., Hvad hvis vi kun skulle skrive erklæringen en gang og være i stand til at ændre året på farten?
det er her den dynamiske s .l kommer i spil.
dynamisk s .l er S .l, der oprettes og udføres på run-time. Det lyder kompliceret, men det er det virkelig ikke. i stedet for at have sætningerne skrevet direkte ind i den lagrede procedure, er S .l-sætningerne først bygget og defineret i variabler.
koden i disse variabler udføres derefter. Lad os nu fortsætte med vores eksempel, her er den samme kode ved hjælp af den dynamiske s .l:
den dynamiske s .l fremhæves med grønt., Dette er S .l, der er bygget til hver @birthYear. Da S .l er bygget, gemmes den i @statement. Det udføres derefter ved hjælp af sp_e .ecutes .l, som vi forklarer nedenfor.
Introduktion til sp_executesql
Du kan bruge sp_executeslq til at udføre transact SQL, der er gemt i en variabel. Udsagnsformularen er
EXECUTE sp_executesql @statement.
Hvis du undrer dig, er sp_e .ecutes .l en systemlagret procedure. De system lagrede procedurer udvide sproget og give flere funktioner for dig at bruge.,
Her er et simpelt eksempel for at prøve:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Hvis du kører denne forespørgsel vindue, du vil få et lignende resultat som dette:
2018-01-24 18:49:30.143
Nu, hvor du har set, hvordan sp_executeslq virker, så lad os sætte det til praksis. Lad os antage, at du er blevet bedt om at skrive en butik procedure, der returnerer enten den gennemsnitlige LineTotal eller summen af LineTotal af ProductID for produkter afsendt i 2011.
din chef foretrækker at have dette skrevet som en gemt procedure. Den lagrede procedure skal acceptere en parameter @ReturnAverage., Hvis det er sandt, returnerer du gennemsnittet, ellers summen.selvfølgelig kan du skrive dette som to separate forespørgsler som vist i den følgende lagrede proc, men det ville ikke være sjovt, da det ville være for meget at skrive og tilbøjelige til fejl!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
den dårlige del her er der er en masse duplikatkode, som jeg har farvet grønt. Der er ikke meget unik kode, men at der er, er farvet rødt.
Med al denne redundans har vi en god mulighed for at vise nogle dynamiske s .l. Lad os gå efter det!,
Her, i stedet for at have to komplette versioner af S .l, en til AVG, den anden til SUM, bygger vi den ønskede version on-the-fly.
s .l er bygget og gemt i variablen @statement. Denne variabel er bygget baseret på parameterværdien @returnAverage. Hvis indstillet til 1, repræsenterer @ – funktionen gennemsnittet; ellers summering.
Du kan se, hvor S .l derefter er bygget til at oprette erklæring. Bemærk farvekodningen. Det skal svare til lignende dele inden for den statiske version; dette skal hjælpe dig med at foretage en sammenligning.,
Debugging Dynamic s .l
Du kan undre dig over, hvordan S .l ser ud ved køretid. Du kan nemt inspicere koden ved hjælp af debuggeren:
kør den lagrede procedure ved hjælp af debugger ‘ s run-kommandoen, og træd derefter ind i koden
fortsæt med at træde ind i koden, indtil du læser Udfør sætningen fremhævet nedenfor.
Når du har nået denne erklæring, hold musen over @erklæring, og når værktøjstip vises, skal du vælge sms-driveren.,
debuggeren er kraftfuld og værd at forstå. Hvis vil meget opfordre dig til at lære mere om det her.
Brug sp_executesql med Parametre
Du kan bruge sp_executesql til reference-parametre i din sætning. Dette gør i sidste ende din kode lettere at læse og giver nogle optimeringsfordele, da udsagnet kan udarbejdes en gang og genbruges mange gange.
udsagnet har form:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
så lad os forklare stykkerne.
- @statement er den s .l, vi ønsker at udføre.,
- @parameterDefinition er en streng, der indeholder en definition af alle parametre, der henvises til i @statement. Hver parameter og type fundet @statement er angivet. Navn og type er adskilt af et mellemrum. Flere parametre er adskilt af et komma.
dernæst indstiller vi parameterværdierne ved at angive parametrene og den ønskede værdi. Parametrene er angivet i rækkefølge defineret i @ parameterDefinition-strengen.
- @parm1 er den første parameter defineret inden for @parameterDefinition-strengen. Værdi er den værdi, du ønsker at indstille den til.,
- @parm2, er den anden parameter, hvis definerer, som angivet i @parameterDefinition.
- og så videre…
Her er et simpelt eksempel, der tilføjer to numre, for at prøve:
De forskellige dele af den erklæring, der er farvekodede:
- @erklæring (grøn) – bemærk der er 2 parametre: @og @b. Også bemærke, at disse ikke er angivet i TSQL. Snarere er de defineret i parameterdefinitionen.
- @parameterDefinition (blå) – hver parameter er defineret som type int.
- parameterværdier (Rød) – vi indstiller parametrene’ værdi her.,
for at afslutte, har vi i dette eksempel en dynamisk udført s .l-sætning, der tilføjer to parametre.
disse parametre er defineret som heltal. Hver parameters værdi er indstillet i kommandoen sp_e .ecutes .l.
eksempel ved hjælp af sp_e .ecutes .l med parametre
lad os tage vores tidligere eksempel og udvide det. I stedet for hardcoding shipDate i forespørgslen, som vi gjorde, lad os bringe det ind som en parameter. Dette gør forespørgslen mere fleksibel og arbejder med andre år end 2011.,
for at foretage denne ændring tilføjer vi en parameter til vores lagrede procedure såvel som den dynamiske forespørgsel. Vi bruger kommandoen sp_e .ecutes .l til at kalde den dynamiske forespørgsel ved hjælp af disse parametre.
den opdaterede lagrede procedure med ændringer er vist nedenfor. Den gemte procedureparameter er grøn, og den dynamiske forespørgselsparameter er rød.
for At køre dette, skal du blot ringe til det uspCalculateSalesSummaryDynamic2 proc fra en forespørgsel windows skal du bruge følgende kommando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Hvis du gør det, vil du se disse resultater.,
Lad mig vise jer et fint forenkling, lad os kombinere @shipDateYear og @shipDate i en parameter. Vi fjerner @shipDateYear fra vores kode. Dette gør det lettere at følge og læse:
Bemærk at UDFØRE erklæring er meget enklere, og der er ingen grund til at tildele den SQL-sætning parameter @shipDateYear til butikken procedure parameter @shipDate værdi.
Dette gør udsagnet mere kompakt og lettere at læse., Forløbet ser ud til at læse bedre, så du ikke er nødt til mentalt at lave forbindelser mellem de lagrede procedure parametre og SQL parametre
Kør Dynamisk SQL med EXECUTE()
Du kan også bruge EXEC eller UDFØRE kommandoen til at køre dynamisk SQL. Formatet for denne kommando
EXECUTE (@statement)
Her er et simpelt eksempel for at prøve:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
Det er vigtigt at vedlægge @sætning i parentes. Hvis du ikke udfører sætningen tager @statement, og i stedet for at køre den dynamiske s .l, mener den, at variabelværdien er navnet på en gemt procedure., Du får følgende fejl:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
selvfølgelig giver dette et godt tip! Hvis du ønsker det, kan du bruge variabler til at specificere, hvilke lagrede procedurer du skal ringe til.
sp_executesql versus UDFØRE
Du kan være undrende, hvorfor bruge sp_executesql versus UDFØRE. Hvad er forskellen mellem de to?
Her er flere grunde til, at Microsoft anbefaler, at du bruger sp_executesql til at køre dynamiske SQL:
- Med UDFØRE alle parametre meget være konverteret fra deres oprindelige type til Unicode., Dette hæmmer optimi .erens evne til at matche den dynamisk byggede s .l med en allerede eksisterende plan.
- Ved hjælp af sp_executesql, optimizer genkender parametre inden for den dynamiske SQL, hvilket gør det lettere for optimizer til at matche planer.
- det er lettere at læse parametriserede forespørgsler, end det er at læse en masse sammenkædet tekst, der inkorporerer dem.parametriserede forespørgsler er mindre tilbøjelige til S .l-injektionsangreb.