după ce ați citit acest articol, veți învăța elementele de bază ale unui SQL dinamic, cum să construiți declarații bazate pe valori variabile și cum să executați declarații construite folosind sp_executesql și executați() dintr-o procedură stocată.
toate exemplele găsite în această lecție se bazează pe Microsoft SQL Server Management Studio și bazele de date eșantion de la AdventureWorks și WideWorldImporters. Puteți începe să utilizați aceste instrumente gratuite folosind ghidul meu, să începeți să utilizați SQL Server.,
construiți SQL dinamic într-o procedură stocată.
multe SQL pe care le scriem sunt scrise Explicit în procedura stocată. Aceasta este ceea ce numim SQL static. Acesta este numit pentru că nu se schimbă. Odată ce este scris, înseamnă că este pus în piatră.
mai jos este un exemplu de SQL static:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
ați observat că există două declarații aici? Fiecare declarație returnează un rezumat al JobTitles pentru un anumit an de naștere angajat. Dacă vrem să adăugăm mai mulți ani de naștere, atunci trebuie să adăugăm mai multe declarații., Ce se întâmplă dacă ar trebui să scriem declarația o singură dată și să putem schimba anul în zbor?
aici intră în joc SQL dinamic.dinamic SQL este SQL care este creat și executat la run-time. Sună complicat, dar într-adevăr nu este. în loc de a avea declarațiile tastat direct în procedura stocată, instrucțiunile SQL sunt construite mai întâi și definite în variabile.
codul din aceste variabile este apoi executat. Acum, să continuăm cu exemplul nostru, aici este același cod folosind SQL dinamic:
SQL dinamic este evidențiat în verde., Acesta este SQL care este construit pentru fiecare @birthYear. Pe măsură ce SQL este construit, acesta este stocat în @declarație. Acesta este apoi executat folosind sp_executesql, pe care le vom explica mai jos.
Introducere în sp_executesql
puteți utiliza sp_executeslq pentru a executa SQL transact stocate într-o variabilă. Formularul de declarație este
EXECUTE sp_executesql @statement.
în cazul În care vă întrebați, sp_executesql este un sistem de proceduri stocate. Procedurile stocate în sistem extind limba și oferă mai multe funcții pentru a fi utilizate.,
Aici este un exemplu simplu pentru a încerca:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Dacă aveți acest lucru în fereastră de interogare, veți obține un rezultat similar ca aceasta:
2018-01-24 18:49:30.143
Acum, că ai văzut cum sp_executeslq lucrări, să-l pună la practică. Să presupunem că ați fost rugat să scrie o procedura de magazin care returnează fie medie LineTotal sau suma de LineTotal de ProductID pentru produsele livrate în 2011.
șeful dvs. ar prefera să aibă acest lucru scris ca o procedură stocată. Procedura stocată ar trebui să accepte un parametru @ReturnAverage., Dacă este adevărat, atunci veți returna media, altfel suma.desigur, puteți scrie acest lucru ca două interogări separate, așa cum se arată în următorul proc stocat, dar asta nu ar fi prea distractiv, deoarece ar fi prea multă tastare și predispusă la erori!
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
partea proastă aici este că există o mulțime de cod duplicat, pe care l-am colorat verde. Nu există mult cod unic, dar că există, este colorat în roșu.cu toate acestea redundanță, avem o mare oportunitate scoate în evidență unele SQL dinamic. Să mergem pentru ea!,
aici, în loc să avem două versiuni complete ale SQL, una pentru AVG, cealaltă pentru SUM, construim versiunea solicitată din mers.
SQL este construit și salvat în variabila @declarație. Această variabilă este construită pe baza valorii parametrului @returnAverage. Dacă este setat la 1, atunci funcția @reprezintă media; în caz contrar, însumarea.
puteți vedea unde SQL este apoi construit pentru a crea declarație. Observați codarea culorilor. Ar trebui să corespundă porțiuni similare în versiunea statică; acest lucru ar trebui să vă ajute să faceți o comparație.,
Debugging dynamic SQL
s-ar putea să vă întrebați cum arată SQL în timpul rulării. Puteți inspecta cu ușurință codul folosind depanatorul:
rulați procedura stocată folosind comanda run a depanatorului, apoi intrați în Cod
Continuați să intrați în cod până când citiți instrucțiunea Execute evidențiată mai jos.
Odată ce ați ajunge la această declarație, treceți peste @declarație, și atunci când vârful instrument este afișat, selectați text visualizer.,depanatorul este puternic și merită înțeles. Dacă v-ar încuraja foarte mult să aflați mai multe despre asta aici.
Utilizarea sp_executesql cu Parametrii
puteți folosi sp_executesql la parametrii de referință în declarație. Acest lucru face în cele din urmă codul dvs. mai ușor de citit și oferă unele beneficii de optimizare, deoarece declarația poate fi compilată o dată și reutilizată de mai multe ori.
instrucțiunea ia forma:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
deci, să explicăm piesele.
- @declarație este SQL dorim să execute.,
- @ parameterDefinition este un șir care conține o definiție a tuturor parametrilor la care se face referire în @statement. Fiecare parametru și tip găsit @declarație este listat. Numele și tipul sunt separate de un spațiu. Parametrii multipli sunt separați printr-o virgulă.
apoi setăm valorile parametrilor, specificând parametrii și valoarea dorită. Parametrii sunt listați în ordinea definită în șirul @ parameterDefinition.
- @parm1 este primul parametru definit în șirul @ parameterDefinition. Valoarea este valoarea pe care doriți să o setați.,
- @parm2, este al doilea parametru, dacă definește, așa cum este declarat în @parameterDefinition.
- și așa mai departe…
Aici este un exemplu simplu, care se adaugă două numere, să încerce:
diferite porțiuni ale declarație sunt coduri de culoare:
- @declarație (verde) – notificare include 2 parametri: @a și @b. Observă de asemenea că acestea nu sunt declarate în TSQL. Mai degrabă, ele sunt definite în definiția parametrilor.
- @ parameterDefinition – Albastru) – fiecare parametru listat este definit ca tip int.
- valorile parametrilor (roșu) – setăm valoarea parametrilor aici.,
pentru a încheia, în acest exemplu avem o instrucțiune SQL executată dinamic care adaugă doi parametri.acești parametri sunt definiți ca numere întregi. Valoarea fiecărui parametru este setată în comanda sp_executesql.
exemplu folosind sp_executesql cu parametrii
să luăm exemplul nostru anterior și să-l extindem. Mai degrabă decât hardcoding shipDate în interogare așa cum am făcut-o, să aducem asta ca parametru. Acest lucru face ca interogarea să fie mai flexibilă și să funcționeze cu alți ani decât 2011.,pentru a face această modificare, vom adăuga un parametru la procedura stocată, precum și interogarea dinamică. Vom folosi comanda sp_executesql pentru a apela interogarea dinamică folosind acești parametri.procedura stocată actualizată cu modificări este prezentată mai jos. Parametrul procedurii stocate este verde, iar parametrul de interogare dinamică este roșu.
Pentru a rula acest lucru, pur și simplu apel uspCalculateSalesSummaryDynamic2 proc dintr-o interogare windows folosind următoarea comandă:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Dacă veți face așa, veți vedea aceste rezultate.,
lasă-mă Să-ți arăt într-o bună simplificare, să combine @shipDateYear și @shipDate într-un singur parametru. Vom elimina @shipDateYear din Codul nostru. Acest lucru face mai ușor de a urmări și citi:
Observați că EXECUTA declarație este mult mai simplă, nu este nevoie pentru a atribui parametrul SQL @shipDateYear la magazin procedura parametru @shipDate lui valoare.acest lucru face ca declarația să fie mai compactă și mai ușor de citit., Fluxul pare să citească mai bine, deoarece nu trebuie să facă mental conexiuni între parametrii de procedură stocate și parametrii SQL
Run dynamic SQL cu EXECUTE()
puteți utiliza, de asemenea, comanda EXEC sau executa pentru a rula SQL dinamic. Formatul pentru această comandă este
EXECUTE (@statement)
Aici este un exemplu simplu pentru a încerca:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
este important să anexați @declarație în paranteză. Dacă nu executați instrucțiunea ia @declarație, și în loc de a rula SQL dinamic, crede că valoarea variabilă este numele unei proceduri stocate., Veți primi următoarea eroare:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
desigur, acest lucru oferă un indiciu excelent! Dacă doriți, puteți utiliza variabile pentru a specifica ce proceduri stocate să apelați.
sp_executesql versus executa
poate vă întrebați De ce utilizați sp_executesql versus executa. Care sunt diferența dintre cele două?
iată câteva motive pentru care Microsoft recomandă utilizarea sp_executesql pentru a rula SQL dinamic:
- cu EXECUTE toți parametrii mult fi convertite de la tipul lor nativ la Unicode., Acest lucru împiedică capacitatea optimizatorului de a potrivi SQL construit dinamic cu un plan preexistent.
- prin utilizarea sp_executesql, Optimizatorul recunoaște parametrii în SQL dinamic, ceea ce face mai ușor pentru optimizator pentru a se potrivi planuri.
- este mai ușor de citit interogări parametrizate decât este de a citi o grămadă de text concatenat care le încorporează.
- interogările parametrizate sunt mai puțin predispuse la atacurile de injecție SQL.