nadat u dit artikel hebt gelezen, leert u de basisprincipes van een dynamische SQL, hoe u statements bouwt op basis van variabele waarden, en hoe u geconstrueerde statements uitvoert met behulp van sp_executesql en EXECUTE() vanuit een opgeslagen procedure.
alle voorbeelden in deze les zijn gebaseerd op de Microsoft SQL Server Management Studio en de voorbeelddatabases van AdventureWorks en WideWorldImporters. U kunt aan de slag met behulp van deze gratis tools met behulp van mijn gids, aan de slag met behulp van SQL Server.,
Bouw dynamische SQL in een opgeslagen procedure.
veel sql die we schrijven wordt expliciet geschreven in de opgeslagen procedure. Dit is wat we de statische SQL noemen. Zo wordt genoemd omdat het niet verandert. Als het eenmaal geschreven is, betekent dat dat het in steen gehamerd is.
Hieronder is een voorbeeld van een statische SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
merkte u dat er hier twee statements zijn? Elke verklaring geeft een samenvatting van jobtitels voor een specifiek werknemer geboortejaar. Als we meer geboortejaren willen toevoegen, dan moeten we meer verklaringen toevoegen., Wat als we de verklaring maar één keer hoeven te schrijven en het jaar on-the-fly kunnen veranderen?
Dit is waar de dynamische SQL in het spel komt.
dynamische SQL is sql die wordt gemaakt en uitgevoerd tijdens runtime. Het klinkt ingewikkeld, maar is het echt niet. in plaats van dat de statements direct in de opgeslagen procedure worden getypt, worden de SQL statements eerst gebouwd en gedefinieerd in variabelen.
de code in deze variabelen wordt dan uitgevoerd. Nu, laten we doorgaan met ons voorbeeld, hier is dezelfde code met behulp van de dynamische SQL:
De dynamische SQL is gemarkeerd in groen., Dit is de SQL die is gebouwd voor elk @birthYear. Als de SQL is gebouwd, wordt het opgeslagen in @statement. Het wordt dan uitgevoerd met behulp van sp_executesql, die we hieronder zullen uitleggen.
Inleiding tot sp_executesql
u kunt sp_executeslq gebruiken om de transactie SQL uit te voeren die is opgeslagen in een variabele. Het statement formulier is
EXECUTE sp_executesql @statement.
in het geval u zich afvraagt, de sp_executesql is een systeem opgeslagen procedure. De systeem opgeslagen procedures breiden de taal uit en bieden meer functies die u kunt gebruiken.,
Hier is een eenvoudig voorbeeld om te proberen:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
als je dit uitvoert in het query venster, krijg je een vergelijkbaar resultaat als dit:
2018-01-24 18:49:30.143
nu je hebt gezien hoe sp_executeslq werkt, laten we het in de praktijk brengen. Laten we aannemen dat u bent gevraagd om een winkel procedure die ofwel de gemiddelde Lijntotaal of som van de Lijntotaal door ProductID voor producten verzonden in 2011 te schrijven.
uw baas zou dit liever geschreven hebben als een opgeslagen procedure. De opgeslagen procedure moet één parameter @Returngemiddelde accepteren., Als het waar is, geef je het gemiddelde terug, anders de som.
natuurlijk zou je dit als twee aparte queries kunnen schrijven, zoals getoond in het volgende opgeslagen proc, maar dat zou niet erg leuk zijn, omdat het te veel typen zou zijn en vatbaar voor fouten!
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
het slechte deel hier is dat er veel dubbele code is, die ik Groen heb gekleurd. Er is niet veel unieke code, maar die er is, is rood gekleurd.
met al deze redundantie hebben we een geweldige kans om een dynamische SQL te laten zien. Laten we ervoor gaan!,
hier, in plaats van twee complete versies van de SQL te hebben, een voor AVG, de andere voor SUM, bouwen we de gevraagde versie on-the-fly.
De SQL wordt gebouwd en opgeslagen in de variabele @statement. Deze variabele is gebouwd op basis van de parameter waarde @returngemiddelde. Indien ingesteld op 1, dan @functie vertegenwoordigt het gemiddelde; anders, sommatie.
u kunt zien waar de SQL vervolgens wordt gebouwd om een statement aan te maken. Let op de kleurcodering. Het moet overeenkomen met soortgelijke delen binnen de statische versie; Dit moet u helpen om een vergelijking te doen.,
debugging Dynamic SQL
u vraagt zich misschien af hoe de SQL er tijdens de looptijd uitziet. U kunt de code eenvoudig inspecteren met de debugger:
Voer de opgeslagen procedure uit met het commando uitvoeren van de debugger en stap vervolgens in de code
stap verder in de code totdat u het onderstaande commando uitvoeren leest.
zodra u dit statement bereikt, beweeg de muisaanwijzer over het @statement en selecteer text visualizer wanneer de gereedschapstip wordt weergegeven.,
de debugger is krachtig en de moeite waard om te begrijpen. Als zou sterk aanmoedigen u meer over te leren hier.
met behulp van sp_executesql met Parameters
kunt u sp_executesql gebruiken om parameters binnen uw statement te refereren. Dit maakt uiteindelijk uw code gemakkelijker te lezen en biedt een aantal optimalisatie voordelen als de verklaring kan eenmaal worden samengesteld en vele malen hergebruikt.
het statement heeft de vorm:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
dus laten we de stukken uitleggen.
- @statement is de SQL die we willen uitvoeren.,
- @parameterDefinition is een string die een definitie bevat van alle parameters waarnaar wordt verwezen in @statement. Elke parameter en type gevonden @ statement wordt vermeld. De naam en het type worden gescheiden door een spatie. Meerdere parameters worden gescheiden door een komma.
vervolgens stellen we de parameterwaarden in, door de parameters en de gewenste waarde op te geven. De parameters worden weergegeven in volgorde gedefinieerd binnen de @ parameterdefinitie string.
- @parm1 is de eerste parameter gedefinieerd binnen de @ parameterdefinitie string. Waarde is de waarde waarop u deze wilt instellen.,
- @parm2, is de tweede parameters, if definieert, zoals gedeclareerd in @parameterDefinition.
- enzovoort …
Hier is een eenvoudig voorbeeld, dat twee getallen toevoegt, om te proberen:
de verschillende delen van het statement zijn kleurgecodeerd:
- @statement (groen) – merk op dat het 2 parameters bevat: @a en @b. merk ook op dat deze niet worden gedeclareerd in TSQL. In plaats daarvan worden ze gedefinieerd in de parameterdefinitie.
- @parameterdefinitie (blauw) – elke vermelde parameter wordt gedefinieerd als type int.
- parameterwaarden (rood) – We stellen hier de waarde van de parameters in.,
om af te ronden, in dit voorbeeld hebben we een dynamisch uitgevoerde SQL statement die twee parameters toevoegt.
deze parameters worden gedefinieerd als gehele getallen. De waarde van elke parameter wordt ingesteld in het sp_executesql Commando.
voorbeeld sp_executesql met Parameters
laten we ons vorige voorbeeld nemen en het uitbreiden. In plaats van het hardcoderen van de shipDate in de query zoals we deden, laten we brengen dat in als een parameter. Dit maakt de zoekopdracht flexibeler en werkt met andere jaren dan 2011.,
om deze wijziging aan te brengen, voegen we een parameter toe aan onze opgeslagen procedure, evenals de dynamische query. We zullen het sp_executesql commando gebruiken om de dynamische query aan te roepen met behulp van deze parameters.
De bijgewerkte opgeslagen procedure met wijzigingen wordt hieronder weergegeven. De opgeslagen procedureparameter is groen en de dynamische query-parameter rood.
om dit uit te voeren, bel je het uspcalculatesalessummarydynamic2 proc vanuit een query windows met het volgende commando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
als je dit doet, zul je deze resultaten zien.,
laat me u een fijne vereenvoudiging tonen, laten we @shipdatear en @shipDate combineren in één parameter. We elimineren @shipdatear uit onze code. Dit maakt het makkelijker om te volgen en te lezen:
merk op dat het EXECUTE statement veel eenvoudiger is, het is niet nodig om de SQL statement parameter @shipdatear toe te wijzen aan de store procedure parameter @shipDate ‘ s value.
Dit maakt het statement compacter en gemakkelijker te lezen., De stroom lijkt beter te lezen, omdat je niet mentaal verbindingen hoeft te maken tussen de opgeslagen procedure parameters en SQL parameters
dynamische SQL uitvoeren met EXECUTE()
Je kunt ook het commando EXEC of EXECUTE gebruiken om dynamische SQL uit te voeren. Het formaat voor dit commando is
EXECUTE (@statement)
Hier is een eenvoudig voorbeeld om te proberen:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
het is belangrijk om @statement tussen haakjes te plaatsen. Als je niet de opdracht uitvoeren neemt @opdracht, en in plaats van het uitvoeren van de dynamische SQL, het denkt dat de variabele waarde is de naam van een opgeslagen procedure., U krijgt de volgende fout:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
natuurlijk geeft dit een geweldige hint! Als u wilt, kunt u variabelen gebruiken om aan te geven welke opgeslagen procedures te bellen.
sp_executesql versus EXECUTE
u vraagt zich misschien af waarom sp_executesql versus EXECUTE wordt gebruikt. Wat is het verschil tussen die twee?
Hier zijn verschillende redenen waarom Microsoft raadt het gebruik van sp_executesql om dynamische SQL draaien:
- Met uitvoeren van alle parameters veel worden geconverteerd van hun eigen type naar Unicode., Dit belemmert het vermogen van de optimizer om de dynamisch gebouwde SQL te matchen met een reeds bestaand plan.
- door sp_executesql te gebruiken, herkent de optimizer de parameters binnen de dynamische SQL, waardoor het voor de optimizer gemakkelijker is om plannen te matchen.
- het is gemakkelijker om geparametreerde query ‘ s te lezen dan om een aantal aaneengeschakelde tekst te lezen waarin ze zijn opgenomen.
- geparametreerde queries zijn minder gevoelig voor SQL-injectieaanvallen.