Etter at du har lest denne artikkelen, vil du få lære det grunnleggende om en dynamisk SQL, hvordan å bygge uttalelser basert på variable verdier, og hvordan å utføre bygget uttrykk ved hjelp av sp_executesql og EXECUTE() fra innen en lagret prosedyre.
Alle eksempler finnes i denne leksjonen er basert på Microsoft SQL Server Management Studio og eksempeldatabaser fra AdventureWorks og WideWorldImporters. Du kan komme i gang med disse gratis verktøyene ved hjelp av min veileder, Komme i Gang ved Hjelp av SQL Server.,
Bygge dynamisk SQL i en lagret prosedyre.
Mange SQL-vi skriv er uttrykkelig skrevet i en lagret prosedyre. Dette er hva vi kan kalle den statiske SQL. Slik er kalt fordi den ikke endres. Når det er skrevet, det betyr at det er satt hamret i stein.
Nedenfor er et eksempel på en statisk SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
Visste du merke til at det er to utsagn her? Hvert utsagn gir en oppsummering av JobTitles for en bestemt ansatt fødselsår. Hvis vi ønsker å legge til mer fødselen år, da må vi legge til flere uttalelser., Hva om vi bare hadde å skrive den setningen en gang og være i stand til å endre år on-the-fly?
Dette er hvor dynamisk SQL kommer inn i bildet.
Dynamisk SQL for SQL som er skapt og utført på kjøre-time. Det høres komplisert ut, men det er det egentlig ikke. I stedet for at de uttalelser som skrives direkte inn i lagret prosedyre, SQL-setninger er bygget og som er definert i variabler.
– koden i disse variablene er deretter utført. Nå, la oss fortsette med vårt eksempel her er den samme koden ved hjelp av dynamisk SQL:
dynamisk SQL er uthevet i grønt., Dette er SQL som er bygget for hver @birthYear. Som SQL er bygget på, den er lagret i @uttalelse. Det er deretter utført ved hjelp av sp_executesql, som vi vil forklare nedenfor.
Introduksjon til sp_executesql
Du kan bruke sp_executeslq å utføre handle SQL lagret i en variabel. Uttalelsen form er
EXECUTE sp_executesql @statement.
I tilfelle du lurer på, er det sp_executesql er et system som er lagret prosedyre. Systemet lagrede prosedyrer utvide språket og tilby flere funksjoner for deg å bruke.,
Her er et enkelt eksempel for å prøve:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Hvis du kjører dette i spørringen vinduet, vil du få et lignende resultat som dette:
2018-01-24 18:49:30.143
Nå som du har sett hvordan sp_executeslq fungerer, la oss legge det til i praksis. La oss anta at du har blitt bedt om å skrive en butikk prosedyre som returnerer enten den gjennomsnittlige LineTotal eller summen av LineTotal av ProductID for produkter som sendes i 2011.
Din sjef foretrekker å ha dette skrevet som en lagret prosedyre. Den lagrede prosedyren bør akseptere en parameter @ReturnAverage., Hvis det stemmer, så vil du gå tilbake gjennomsnittet, ellers summen.
selvfølgelig, du kan skrive dette som to separate spørsmål, som vist i følgende lagret proc men som ikke ville bli mye moro, så det ville være for mye å skrive og utsatt for feil!
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 delen her er at det er mange duplikat kode, som jeg har farget grønn. Det er ikke mye en unik kode, men som det er, er farget rødt.
Med alt dette redundans, har vi fått en flott mulighet vise frem noen dynamisk SQL. La oss gå for det!,
Her, i stedet for å ha to komplette versjoner av SQL, en for AVG, den andre for SUM, vi bygger den forespurte versjon on-the-fly.
SQL er bygget og lagret i variabelen @uttalelse. Denne variabelen er basert på parameteren verdi @returnAverage. Hvis verdien angis til 1, deretter @funksjon representerer den Gjennomsnittlige; ellers, Summering.
Du kan se hvor SQL er bygget deretter å opprette uttalelse. Legg merke til fargen koding. Det skal tilsvare tilsvarende deler i den statiske versjonen; dette skal hjelpe deg å gjøre en sammenligning.,
Debugging Dynamisk SQL
kan Du være lurer på hva SQL ser ut som på kjøre-time. Du kan enkelt inspisere koden ved å bruke feilsøkingsprogrammet:
Kjøre den lagrede prosedyren ved å bruke feilsøkingsprogrammet kjør-kommandoen, og deretter gå Inn koden
Fortsett for å gå Inn i koden til du lese Utføre uttalelse er uthevet nedenfor.
Når du har nådd denne uttalelsen, holder du musepekeren over @uttalelse, og når verktøytips vises, velger du tekst visualizer.,
feilsøkingsprogrammet er kraftig og verdt å forstå. Hvis du vil sterkt oppfordre deg til å lære mer om det her.
ved Hjelp av sp_executesql med Parametere
Du kan bruke sp_executesql til referanse parametere innenfor deres uttalelse. Dette til syvende og sist gjør koden lettere å lese, og gir noen optimalisering fordeler som uttalelsen kan samles inn én gang og om igjen mange ganger.
uttalelsen tar form:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Så la oss forklare stykker.
- @uttalelse er SQL-vi ønsker å utføre.,
- @parameterDefinition er en streng som inneholder en definisjon av alle parametere som det refereres til i @uttalelse. Hver parameter type og funnet @uttalelse er oppført. Navn og type er atskilt med et mellomrom. Flere parametere er atskilt med et komma.
Neste vi angi parameterverdier, ved å spesifisere parametere og ønsket verdi. Parametrene er oppført på listen i rekkefølgen som er definert innenfor @parameterDefinition strengen.
- @parm1 er den første parameteren er definert innenfor @parameterDefinition strengen. Verdien er den verdien du ønsker å sette den til.,
- @parm2, er den andre parametre, hvis definerer, som erklærte i @parameterDefinition.
- og så videre…
Her er et enkelt eksempel, som legger to tall, for å prøve:
De ulike deler av uttalelsen er fargekodet:
- @uttalelse (grønn) – legg merke til det inkluderer 2 parametre: @a @b. Legg også merke til disse er ikke deklarert i TSQL. Snarere, de er definert i parameteren definisjon.
- @parameterDefinition (blå) – hver parameter listen er definert som type int.
- parameterverdier (red) – vi setter parametrene’ – verdien her.,
for Å bryte opp, i dette eksempelet har vi en dynamisk utført SQL-setning som add to parametere.
Disse parametrene er definert som heltall. Hver parameter er verdien er satt i sp_executesql kommando.
Eksempel ved hjelp av sp_executesql med Parametere
La oss ta vår forrige eksempel og forlenge den. Snarere enn hardcoding shipDate i spørringen som vi gjorde, la oss ta det inn som en parameter. Dette gjør søket mer fleksibel og fungerer med andre år enn 2011.,
for Å gjøre denne endringen, vil vi legge til en parameter til vår lagret prosedyre, samt dynamisk spørring. Vi vil bruke sp_executesql-kommandoen til å ringe dynamisk spørring ved hjelp av disse parametrene.
Den oppdaterte lagret prosedyre med endringer er vist nedenfor. Den lagrede prosedyren parameteren er grønn, og den dynamiske søkeparameter rødt.
for Å kjøre denne, rett og slett ringe uspCalculateSalesSummaryDynamic2 proc fra en spørring windows ved hjelp av følgende kommando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Hvis du gjør det, vil du se disse resultatene.,
La meg vise deg en fin forenkling, la oss kombinere @shipDateYear og @shipDate til en parameter. Vi vil eliminere @shipDateYear fra vår kode. Dette gjør det enklere å følge med og lese:
legg Merke til at UTFØRE uttalelse er mye enklere, det er ikke nødvendig å tilordne SQL-setningen parameter @shipDateYear til butikken prosedyre parameter @shipDate verdi.
Dette gjør påstanden mer kompakt og enklere å lese., Strømmen virker å lese bedre, så du trenger ikke å mentalt lage forbindelser mellom den lagrede prosedyren parametere og SQL parametere
Kjør Dynamisk SQL med EXECUTE()
Du kan også bruke EXEC eller UTFØR kommando for å kjøre dynamisk SQL. Formatet for denne kommandoen er
EXECUTE (@statement)
Her er et enkelt eksempel for å prøve:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
Det er viktig å legge @uttalelse i parentes. Hvis du ikke UTFØR uttalelse tar @uttalelse, og i stedet for å kjøre dynamisk SQL, det tror variabel verdi er navnet på en lagret prosedyre., Du vil få følgende feilmelding:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
selvfølgelig, dette gir en flott tips! Hvis du vil, kan du bruke variabler til å angi hvilke lagrede prosedyrer for å ringe.
sp_executesql versus UTFØR
lurer Du kanskje på hvorfor bruke sp_executesql versus UTFØRE. Hva er forskjellen mellom de to?
Her er flere grunner til at Microsoft anbefaler at du bruker sp_executesql å kjøre dynamisk SQL:
- Med UTFØRE alle parametere mye konverteres fra sitt eget skriv til Unicode., Dette hemme optimizer er evnen til å matche dynamisk bygget SQL med en pre-eksisterende plan.
- Ved hjelp av sp_executesql, optimizer gjenkjenner parametre innenfor dynamisk SQL, noe som gjør det enklere for optimizer å matche planer.
- Det er lettere å lese parameterized spørsmål enn det er å lese en haug av sammenhengende tekst som inneholder dem.
- Parameterized spørringer er mindre utsatt for å SQL-injection angrep.