miután elolvasta ezt a cikket, akkor kap, hogy megtanulják az alapokat a dinamikus SQL, hogyan kell építeni nyilatkozatok alapján változó értékeket, és hogyan kell végrehajtani épített nyilatkozatok segítségével sp_executesql és végre() belül tárolt eljárás.
Az ebben a leckében található összes példa a Microsoft SQL Server Management Studio és az AdventureWorks és a WideWorldImporters mintaadatbázisain alapul. Elkezdheti használni ezeket az ingyenes eszközöket a My guide használatával, az SQL Server használatával.,
dinamikus SQL készítése egy tárolt eljárásban.
sok általunk írt SQL kifejezetten be van írva a tárolt eljárásba. Ezt nevezzük statikus SQL-nek. Az ilyen név azért van, mert nem változik. Ha egyszer meg van írva, ez azt jelenti, hogy kőbe van vágva.
Az alábbiakban egy statikus SQL példa látható:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
észrevetted, hogy itt két állítás van? Minden nyilatkozat visszaadja a munka összefoglalásátcímek egy adott munkavállalói születési évre. Ha több születési évet akarunk hozzáadni, akkor további nyilatkozatokat kell hozzáadnunk., Mi van, ha csak egyszer kell megírnunk a nyilatkozatot, és meg tudjuk változtatni az évet menet közben?
itt jön létre a dinamikus SQL.
A dinamikus SQL az SQL, amelyet futásidőben hoznak létre és hajtanak végre. Bonyolultnak hangzik, de valójában nem az. ahelyett, hogy a kijelentéseket közvetlenül a tárolt eljárásba gépelnék, az SQL utasítások először változókban vannak felépítve és definiálva.
a változók kódja ezután végrehajtásra kerül. Most folytassuk a példánkat, itt ugyanaz a kód a dinamikus SQL használatával:
a dinamikus SQL zöld színnel van kiemelve., Ez az SQL, amely minden @birthYear számára készült. Mivel az SQL épül, ez tárolja @utasítás. Ezután az sp_executesql segítségével hajtják végre, amelyet az alábbiakban ismertetünk.
Bevezetés a sp_executesql
használhatja sp_executeslq végrehajtani a tranzakció SQL belül tárolt változó. A nyilatkozat formája
EXECUTE sp_executesql @statement.
abban az esetben, ha kíváncsi, a sp_executesql egy rendszer tárolt eljárás. A rendszer által tárolt eljárások kiterjesztik a nyelvet, és további funkciókat biztosítanak az Ön számára.,
itt van egy egyszerű példa a kipróbálásra:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Ha ezt a lekérdezési ablakban futtatja, hasonló eredményt kap, mint ez:
2018-01-24 18:49:30.143
most, hogy látta, hogyan működik a sp_executeslq, tegyük a gyakorlatba. Tegyük fel, hogy felkérték Önt, hogy írjon egy olyan áruházi eljárást, amely a 2011-ben szállított termékek esetében a LineTotal átlagos összegét vagy a Linetotal összegét adja vissza ProductID szerint.
a főnöke inkább ezt tárolja. A tárolt eljárásnak el kell fogadnia egy @ReturnAverage paramétert., Ha igaz, akkor visszaadja az átlagot, különben az összeget.
természetesen ezt két különálló lekérdezésként is meg lehet írni, amint az a következő tárolt proc-ban látható, de ez nem lenne sok móka, mivel túl sok gépelés lenne, és hajlamos a hibákra!
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
a rossz rész itt van egy csomó ismétlődő kód, amelyet zöldre színeztem. Nincs sok egyedi kód, de van, piros színű.
ezzel a redundanciával nagyszerű lehetőségünk van néhány dinamikus SQL bemutatására. Hajrá!,
itt ahelyett, hogy az SQL két teljes verziója lenne, az egyik az AVG-hez, a másik a SUM-hoz, a kért verziót on-the-fly-re építjük.
az SQL be van építve a @utasítás változóba. Ez a változó a @returnAverage paraméterérték alapján épül fel. Ha 1-re van állítva, akkor a @funkció az átlagot jelenti; egyébként Összegzés.
láthatjuk, ahol az SQL majd épül, hogy hozzon létre nyilatkozatot. Figyelje meg a színkódolást. Meg kell felelnie a hasonló részek a statikus változat; ez segít csinálni egy összehasonlítás.,
hibakeresés dinamikus SQL
lehet, hogy vajon mi az SQL néz ki futási időben. Könnyen ellenőrizheti a kódot a hibakereső segítségével:
futtassa a tárolt eljárást a hibakereső Futtatás parancsával, majd lépjen be a kódba
folytassa a lépést a kódba, amíg el nem olvassa az alább kiemelt végrehajtási utasítást.
miután elérte ezt az állítást, vigye az egérmutatót a @utasítás fölé, és amikor megjelenik az eszköz csúcsa, válassza a text visualizer lehetőséget.,
a hibakereső erős, érdemes megérteni. Ha nagyon bátorítana, itt többet megtudhat róla.
segítségével sp_executesql paraméterekkel
használhatja sp_executesql referencia paraméterek belül a nyilatkozatot. Ez végső soron megkönnyíti a kód olvasását, valamint bizonyos optimalizálási előnyöket biztosít, mivel a nyilatkozat egyszer összeállítható, sokszor újra felhasználható.
a nyilatkozat A következő formában jelenik meg:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
tehát magyarázzuk meg a darabokat.
- @ utasítás az SQL szeretnénk végrehajtani.,
- @ parameterDefinition egy string, amely meghatározza az összes paramétert hivatkozott @statement. Minden paraméter és típus megtalálható @utasítás szerepel. A nevet és a típust szóköz választja el egymástól. Több paramétert vesszővel választunk el.
ezután a paraméterek és a kívánt érték megadásával állítjuk be a paraméterértékeket. A paraméterek a @parameterDefinition karakterláncban meghatározott sorrendben vannak felsorolva.
- @ parm1 az első paraméter a @parameterDefinition karakterláncban. Az érték az az érték, amelyet be szeretne állítani.,
- @ parm2, a második paraméter, ha meghatározza, a @parameterDefinition deklarált módon.
- stb…
Itt egy egyszerű példa, amely összead két számot, hogy próbálja ki:
A különböző részein a nyilatkozat színkóddal:
- @nyilatkozat (zöld) – hirdetmény tartalmazza 2 paraméterek: @valamint @b. Emellett azt is vegyük észre, hogy ezek nem bejelentett a TSQL. Inkább a paraméter definíciójában vannak meghatározva.
- @ parameterDefinition (kék) – minden felsorolt paraméter int típusú.
- paraméterértékek (piros) – itt állítjuk be a paraméterek értékét.,
a lezáráshoz ebben a példában van egy dinamikusan végrehajtott SQL utasítás, amely két paramétert ad hozzá.
ezeket a paramétereket egész számként definiáljuk. Minden paraméter értéke az sp_executesql parancsban van beállítva.
példa sp_executesql használatával paraméterekkel
vegyük az előző példát, és terjesszük ki. Ahelyett, hogy a shipDate-t a lekérdezésbe kódolnánk, mint mi, ezt paraméterként hozzuk be. Ez rugalmasabbá teszi a lekérdezést, valamint a 2011-től eltérő évekkel való együttműködést.,
ennek a változásnak a végrehajtásához hozzáadunk egy paramétert a tárolt eljáráshoz, valamint a dinamikus lekérdezéshez. Az sp_executesql parancs segítségével hívjuk fel a dinamikus lekérdezést ezekkel a paraméterekkel.
a frissített tárolt eljárás változásokkal az alábbiakban látható. A tárolt eljárás paraméter zöld, a dinamikus lekérdezési paraméter piros.
ennek futtatásához egyszerűen hívja az uspCalculateSalesSummaryDynamic2 proc-ot egy lekérdező ablakból a következő paranccsal:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Ha ezt megteszi, akkor ezeket az eredményeket látja.,
hadd mutassak meg egy finom egyszerűsítést, kombináljunk @shipDateYear és @shipDate egy paramétert. Megszüntetjük a @shipDateYear – t a kódunkból. Ez megkönnyíti a követést és olvasást:
vegye figyelembe, hogy az EXECUTE utasítás sokkal egyszerűbb, nincs szükség az SQL utasítás paraméter @shipDateYear hozzárendelésére a store procedure paraméterhez @shipDate értéke.
Ez tömörebbé és könnyebben olvashatóvá teszi a kijelentést., Úgy tűnik, hogy az áramlás jobban olvasható, mivel nem kell mentálisan kapcsolatot létesítenie a tárolt eljárás paraméterei és az SQL paraméterek között
futtassa a dinamikus SQL-t Exec()
a EXEC vagy EXECUTE parancsot a dinamikus SQL futtatásához is használhatja. Ennek a parancsnak a formátuma
EXECUTE (@statement)
itt egy egyszerű példa a kipróbálásra:
fontos zárójelben mellékelni @nyilatkozatot. Ha nem az EXECUTE utasítás veszi @utasítás, ahelyett, hogy fut a dinamikus SQL, azt hiszi, a változó értéke a neve egy tárolt eljárás., A következő hibát kapja:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
természetesen ez nagyszerű tippet nyújt! Ha szeretné, változók segítségével megadhatja, hogy mely tárolt eljárásokat hívja.
sp_executesql versus EXECUTE
lehet, hogy vajon miért használja sp_executesql versus EXECUTE. Mi a különbség a kettő között?
itt van néhány ok, amiért a Microsoft azt javasolja, hogy a sp_executesql dinamikus SQL futtatásához:
- az összes paraméter végrehajtásával sok konvertálható a natív típusról Unicode-ra., Ez akadályozza az optimalizáló azon képességét, hogy a dinamikusan felépített SQL-t egy már meglévő tervhez illessze.
- segítségével sp_executesql, az optimalizáló felismeri a paramétereket a dinamikus SQL, így könnyebb az optimalizáló, hogy megfeleljen tervek.
- könnyebb olvasni a paraméterezett lekérdezéseket, mint egy csomó összefűzött szöveget olvasni, amely magában foglalja őket.
- a paraméterezett lekérdezések kevésbé hajlamosak az SQL injection támadásokra.