När du har läst den här artikeln får du lära dig grunderna i en dynamisk SQL, hur man bygger uttalanden baserade på variabla värden och hur man utför konstruerade uttalanden med sp_executesql och exekvera() inifrån en lagrad procedur.
alla exempel som finns i den här lektionen är baserade på Microsoft SQL Server Management Studio och provdatabaserna från AdventureWorks och Wideworldimportrar. Du kan komma igång med dessa gratis verktyg med hjälp av min guide, komma igång med SQL Server.,
Bygg dynamisk SQL i en lagrad procedur.
många SQL vi skriver är uttryckligen skrivet i den lagrade proceduren. Detta är vad vi kallar den statiska SQL. Sådan heter eftersom det inte förändras. När det är skrivet betyder det att det är insatt i sten.
nedan är ett exempel 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
märkte du att det finns två uttalanden här? Varje uttalande returnerar en sammanfattning av arbetstider för en viss anställd födelseår. Om vi vill lägga till fler födelseår måste vi lägga till fler uttalanden., Vad händer om vi bara var tvungna att skriva uttalandet en gång och kunna ändra året on-the-fly?
det är här den dynamiska SQL kommer till spel.
dynamisk SQL är SQL som skapas och körs vid körning. Det låter komplicerat, men det är det verkligen inte. istället för att ha uttalandena skrivna direkt i den lagrade proceduren byggs SQL-satserna först och definieras i variabler.
koden i dessa variabler exekveras sedan. Nu fortsätter vi med vårt exempel, här är samma kod med den dynamiska SQL:
den dynamiska SQL är markerad i grönt., Detta är SQL som är byggd för varje @birthYear. Eftersom SQL är byggd, lagras den i @statement. Det körs sedan med hjälp av sp_executesql, som vi kommer att förklara nedan.
introduktion till sp_executesql
Du kan använda sp_executeslq för att utföra transact SQL lagras i en variabel. Formuläret för uttalande är
EXECUTE sp_executesql @statement.
om du undrar är sp_executesql ett system lagrat förfarande. Systemet lagrade procedurer utöka språket och ge fler funktioner för dig att använda.,
här är ett enkelt exempel att prova:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
om du kör detta i frågefönstret får du ett liknande resultat så här:
2018-01-24 18:49: 30.143
nu när du har sett hur sp_executeslq fungerar, låt oss uttrycka det för att träna. Låt oss anta att du har blivit ombedd att skriva en butik procedur som returnerar antingen den genomsnittliga LineTotal eller summan av LineTotal av ProductID för produkter som levereras under 2011.
din chef föredrar att ha detta skrivet som en lagrad procedur. Den lagrade proceduren bör acceptera en parameter @Returgenomsnitt., Om det är sant kommer du att returnera genomsnittet, annars summan.
naturligtvis kan du skriva detta som två separata frågor som visas i följande lagrade proc men det skulle inte vara mycket roligt, eftersom det skulle vara för mycket att skriva och benägna att fel!
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åliga delen här är att det finns mycket dubblettkod, som jag har färgat grön. Det finns inte mycket unik kod, men att det finns, är färgad röd.
med all denna redundans, vi har en stor möjlighet visa upp några dynamiska SQL. Nu kör vi!,
här, istället för att ha två fullständiga versioner av SQL, en för AVG, den andra för summan, bygger vi den begärda versionen on-the-fly.
SQL byggs och sparas i variabeln @ – satsen. Denna variabel är byggd baserat på parametervärdet @returngenomsnitt. Om den är inställd på 1, representerar @ – funktionen medelvärdet; annars summering.
Du kan se var SQL sedan byggs för att skapa uttalande. Märker färgkodning. Det bör motsvara liknande delar inom den statiska versionen; detta bör hjälpa dig att göra en jämförelse.,
felsökning dynamisk SQL
Du kanske undrar hur SQL ser ut vid körning. Du kan enkelt inspektera koden med hjälp av felsökaren:
kör den lagrade proceduren med debuggerns körkommando och gå sedan in i koden
fortsätt att kliva in i koden tills du läser Exekveringsuttalandet markerat nedan.
När du når detta uttalande, håll muspekaren över @ – uttalandet och när verktygstipset visas väljer du text visualizer.,
debugger är kraftfull och värt att förstå. Om skulle uppmuntra dig att lära dig mer om det här.
använda sp_executesql med parametrar
Du kan använda sp_executesql för att referera till parametrar i ditt uttalande. Detta gör i slutändan din kod lättare att läsa och ger vissa optimeringsfördelar som uttalandet kan sammanställas en gång och återanvändas många gånger.
uttalandet tar formen:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
så låt oss förklara bitarna.
- @statement är SQL vi vill köra.,
- @parameterDefinition är en sträng som innehåller en definition av alla parametrar som refereras i @statement. Varje parameter och typ hittades @statement listas. Namnet och typen är åtskilda av ett mellanslag. Flera parametrar separeras med ett kommatecken.
därefter ställer vi in parametervärdena genom att ange parametrarna och önskat värde. Parametrarna listas i den ordning som definieras i strängen @parameterDefinition.
- @parm1 är den första parametern som definieras i strängen @parameterDefinition. Värdet är värdet, du vill ställa in det på.,
- @parm2, är de andra parametrarna, if definierar, som deklareras i @parameterDefinition.
- och så vidare…
Här är ett enkelt exempel, som lägger till två nummer, för att försöka:
de olika delarna av uttalandet är färgkodade:
- @statement (grön) – Lägg märke till att det innehåller 2 parametrar: @a och @B. lägg också märke till att dessa inte deklareras i TSQL. Snarare definieras de i parameterdefinitionen.
- @parameterDefinition (Blå) – varje parameter som anges definieras som typ int.
- parametervärden (röd) – vi ställer in parameterns värde här.,
för att avsluta, i det här exemplet har vi en dynamiskt utförd SQL-sats som lägger till två parametrar.
dessa parametrar definieras som heltal. Varje parameters värde anges i kommandot sp_executesql.
exempel med sp_executesql med parametrar
låt oss ta vårt tidigare exempel och utöka det. I stället för att hårdkoda fartyget i frågan som vi gjorde, låt oss ta in det som en parameter. Detta gör frågan mer flexibel och att arbeta med andra år än 2011.,
för att göra denna ändring lägger vi till en parameter i vår lagrade procedur, liksom den dynamiska frågan. Vi använder kommandot sp_executesql för att ringa den dynamiska frågan med hjälp av dessa parametrar.
den uppdaterade lagrade proceduren med ändringar visas nedan. Parametern lagrad procedur är grön och parametern dynamisk sökfråga röd.
för att köra det här, ring bara uspcalculatesalessummarydynamic2 proc från en frågefönster med följande kommando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
om du gör det ser du dessa resultat.,
Låt mig visa dig en fin förenkling, låt oss kombinera @shipDateYear och @shipDate till en parameter. Vi eliminerar @ shipDateYear från vår kod. Detta gör det lättare att följa och läsa:
Observera att exekveringsdeklarationen är mycket enklare, det finns ingen anledning att tilldela SQL-satsparametern @shipDateYear till Store procedure parametern @shipdates värde.
detta gör uttalandet mer kompakt och lättare att läsa., Flödet verkar läsa bättre, eftersom du inte behöver mentalt göra anslutningar mellan de lagrade procedurparametrarna och SQL-parametrar
kör dynamisk SQL med EXECUTE ()
Du kan också använda kommandot EXEC eller EXECUTE för att köra dynamisk SQL. Formatet för det här kommandot är
EXECUTE (@statement)
Här är ett enkelt exempel att prova:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
det är viktigt att bifoga @statement inom parentes. Om du inte kör satsen tar @statement, och istället för att köra den dynamiska SQL, det tror att variabelvärdet är namnet på en lagrad procedur., Du får följande fel:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
naturligtvis ger detta en bra ledtråd! Om du vill kan du använda variabler för att ange vilka lagrade procedurer som ska anropas.
sp_executesql versus EXECUTE
Du kanske undrar varför använda sp_executesql versus EXECUTE. Vad är skillnaden mellan de två?
Här är flera anledningar till varför Microsoft rekommenderar att du använder sp_executesql för att köra dynamisk SQL:
- med kör alla parametrar mycket konverteras från sin ursprungliga typ till Unicode., Detta hindrar optimeringens förmåga att matcha den dynamiskt byggda SQL med en befintlig plan.
- genom att använda sp_executesql känner optimizer igen parametrarna i den dynamiska SQL, vilket gör det lättare för optimizer att matcha planer.
- Det är lättare att läsa parametriserade frågor än det är att läsa en massa sammanlänkad text som innehåller dem.
- parametriserade frågor är mindre benägna att SQL injection attacker.