Dopo aver letto questo articolo, imparerai le basi di un SQL dinamico, come costruire istruzioni basate su valori variabili e come eseguire istruzioni costruite usando sp_executesql ed EXECUTE() all’interno di una stored procedure.
Tutti gli esempi trovati in questa lezione sono basati su Microsoft SQL Server Management Studio e sui database di esempio di AdventureWorks e WideWorldImporters. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida, Per iniziare a utilizzare SQL Server.,
Crea SQL dinamico in una stored procedure.
Molti SQL che scriviamo sono scritti esplicitamente nella stored procedure. Questo è ciò che chiamiamo SQL statico. Tale è chiamato perché non cambia. Una volta che è scritto, ciò significa che è impostato-martellato in pietra.
Di seguito è riportato un esempio di SQL statico:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
Hai notato che ci sono due istruzioni qui? Ogni dichiarazione restituisce un riepilogo dei titoli di lavoro per uno specifico anno di nascita del dipendente. Se vogliamo aggiungere più anni di nascita, allora dobbiamo aggiungere più dichiarazioni., E se dovessimo scrivere la dichiarazione solo una volta ed essere in grado di cambiare l’anno al volo?
È qui che entra in gioco l’SQL dinamico.
SQL dinamico è SQL che viene creato ed eseguito in fase di esecuzione. Sembra complicato, ma in realtà non lo è. Invece di avere le istruzioni digitate direttamente nella stored procedure, le istruzioni SQL vengono prima costruite e definite in variabili.
Il codice in queste variabili viene quindi eseguito. Ora, continuiamo con il nostro esempio, ecco lo stesso codice che utilizza l’SQL dinamico:
L’SQL dinamico è evidenziato in verde., Questo è l’SQL creato per ogni @ birthYear. Come l’SQL è costruito, è memorizzato nella dichiarazione@. Viene quindi eseguito utilizzando sp_executesql, che spiegheremo di seguito.
Introduzione a sp_executesql
È possibile utilizzare sp_executeslq per eseguire il transact SQL memorizzato all’interno di una variabile. Il modulo di istruzione è
EXECUTE sp_executesql @statement.
Nel caso ve lo stiate chiedendo, sp_executesql è una stored procedure di sistema. Le stored procedure di sistema estendono la lingua e forniscono più funzionalità da utilizzare.,
Ecco un semplice esempio da provare:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Se lo esegui nella finestra della query, otterrai un risultato simile come questo:
2018-01-24 18:49:30.143
Ora che hai visto come funziona sp_executeslq, mettiamolo in pratica. Supponiamo che ti sia stato chiesto di scrivere una procedura di negozio che restituisca il LineTotal medio o la somma di LineTotal per ProductID per i prodotti spediti nel 2011.
Il tuo capo preferirebbe averlo scritto come stored procedure. La stored procedure dovrebbe accettare un parametro @ ReturnAverage., Se è vero, restituirai la media, altrimenti la somma.
Ovviamente, potresti scrivere questo come due query separate come mostrato nel seguente proc memorizzato, ma non sarebbe molto divertente, in quanto sarebbe troppo digitando e soggetto a errori!
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
La parte negativa qui è che c’è un sacco di codice duplicato, che ho colorato di verde. Non c’è molto codice univoco, ma che c’è, è colorato di rosso.
Con tutta questa ridondanza, abbiamo una grande opportunità di mostrare un po ‘ di SQL dinamico. Andiamo per esso!,
Qui, invece di avere due versioni complete di SQL, una per AVG, l’altra per SUM, costruiamo la versione richiesta al volo.
L’SQL è costruito e salvato nella variabile @istruzione. Questa variabile è costruita in base al valore del parametro @returnAverage. Se impostato su 1, la funzione @ rappresenta la Media; altrimenti, Somma.
Puoi vedere dove viene creato l’SQL per creare l’istruzione. Notare la codifica a colori. Dovrebbe corrispondere porzioni simili all’interno della versione statica; questo dovrebbe aiutarti a fare un confronto.,
Debug SQL dinamico
Potresti chiederti come appare SQL in fase di esecuzione. È possibile ispezionare facilmente il codice utilizzando il debugger:
Eseguire la stored procedure utilizzando il comando run del debugger, quindi Inserire il codice
Continuare a Inserire il codice fino a leggere l’istruzione Execute evidenziata di seguito.
Una volta raggiunta questa istruzione, passare il mouse sopra l’istruzione@, e quando viene visualizzato il suggerimento, selezionare visualizzatore di testo.,
Il debugger è potente e vale la pena capire. Se ti incoraggerebbe molto a saperne di più qui.
Usando sp_executesql con i parametri
Puoi usare sp_executesql per fare riferimento ai parametri all’interno della tua istruzione. Ciò rende il codice più facile da leggere e fornisce alcuni vantaggi di ottimizzazione in quanto l’istruzione può essere compilata una volta e riutilizzata molte volte.
L’istruzione assume la forma:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Quindi spieghiamo i pezzi.
- @statement è l’SQL che vogliamo eseguire.,
- @parameterDefinition è una stringa contenente una definizione di tutti i parametri a cui si fa riferimento nell’istruzione@. Ogni parametro e tipo trovato @ istruzione è elencato. Il nome e il tipo sono separati da uno spazio. Più parametri sono separati da una virgola.
Successivamente impostiamo i valori dei parametri, specificando i parametri e il valore desiderato. I parametri sono elencati nell’ordine definito all’interno della stringa @ parameterDefinition.
- @parm1 è il primo parametro definito all’interno della stringa @parameterDefinition. Valore è il valore su cui si desidera impostarlo.,
- @parm2, è il secondo parametro, se definisce, come dichiarato in @ parameterDefinition.
- e così via
Ecco un semplice esempio, che aggiunge due numeri, per provare:
Le varie parti dell’istruzione sono codificate a colori:
- @statement (verde) – nota che include 2 parametri: @a e @b. Nota anche che questi non sono dichiarati in TSQL. Piuttosto, sono definiti nella definizione dei parametri.
- @parameterDefinition (blu) – ogni parametro elencato è definito come tipo int.
- Parameter values (red) – impostiamo qui il valore dei parametri.,
Per concludere, in questo esempio abbiamo un’istruzione SQL eseguita dinamicamente che aggiunge due parametri.
Questi parametri sono definiti come numeri interi. Il valore di ogni parametro è impostato nel comando sp_executesql.
Esempio utilizzando sp_executesql con parametri
Prendiamo il nostro esempio precedente ed estenderlo. Piuttosto che codificare la shipDate nella query come abbiamo fatto, inseriamola come parametro. Ciò rende la query più flessibile e funziona con anni diversi dal 2011.,
Per apportare questa modifica, aggiungeremo un parametro alla nostra stored procedure, così come la query dinamica. Useremo il comando sp_executesql per chiamare la query dinamica utilizzando questi parametri.
La stored procedure aggiornata con le modifiche è mostrata di seguito. Il parametro stored procedure è verde e il parametro dynamic query rosso.
Per eseguire questo, è sufficiente chiamare il uspCalculateSalesSummaryDynamic2 proc da una finestra di query utilizzando il seguente comando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Se lo fai, vedrai questi risultati.,
Lascia che ti mostri una bella semplificazione, combiniamo @shipDateYear e @shipDate in un unico parametro. Elimineremo @shipDateYear dal nostro codice. Ciò rende più facile seguire e leggere:
Si noti che l’istruzione EXECUTE è molto più semplice, non è necessario assegnare il parametro dell’istruzione SQL @shipDateYear al parametro della procedura di archiviazione @shipDate valore.
Questo rende la dichiarazione più compatta e più facile da leggere., Il flusso sembra leggere meglio, in quanto non è necessario effettuare connessioni mentali tra i parametri della stored procedure e i parametri SQL
Esegui SQL dinamico con EXECUTE()
Puoi anche utilizzare il comando EXEC o EXECUTE per eseguire SQL dinamico. Il formato per questo comando è
EXECUTE (@statement)
Ecco un semplice esempio da provare:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
È importante racchiudere @statement tra parentesi. Se non lo fai, l’istruzione EXECUTE accetta l’istruzione @e invece di eseguire l’SQL dinamico, pensa che il valore della variabile sia il nome di una stored procedure., Si otterrà il seguente errore:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
Naturalmente, questo fornisce un ottimo suggerimento! Se lo si desidera, è possibile utilizzare le variabili per specificare quali stored procedure chiamare.
sp_executesql versus EXECUTE
Ci si potrebbe chiedere perché usare sp_executesql contro EXECUTE. Quali sono le differenze tra i due?
Qui ci sono diversi motivi per cui Microsoft consiglia di utilizzare sp_executesql per eseguire SQL dinamico:
- Con ESEGUIRE tutti i parametri molto essere convertiti dal loro tipo nativo in Unicode., Ciò ostacola la capacità dell’ottimizzatore di abbinare l’SQL costruito dinamicamente con un piano preesistente.
- Utilizzando sp_executesql, l’ottimizzatore riconosce i parametri all’interno dell’SQL dinamico, rendendo più facile per l’ottimizzatore abbinare i piani.
- È più facile leggere le query parametrizzate che leggere un gruppo di testo concatenato che le incorpora.
- Le query parametrizzate sono meno soggette agli attacchi SQL injection.