Nachdem Sie diesen Artikel gelesen haben, lernen Sie die Grundlagen eines dynamischen SQL kennen, wie Sie Anweisungen basierend auf Variablenwerten erstellen und konstruierte Anweisungen mit sp_executesql und EXECUTE() innerhalb einer gespeicherten Prozedur ausführen.
Alle Beispiele in dieser Lektion basieren auf dem Microsoft SQL Server Management Studio und den Beispieldatenbanken von AdventureWorks und WideWorldImporters. Sie können mit diesen kostenlosen Tools mit meinem Handbuch beginnen und mit SQL Server beginnen.,
Erstellen Sie dynamisches SQL in einer gespeicherten Prozedur.
Viele SQL schreiben wir explizit in die gespeicherte Prozedur geschrieben. Dies nennen wir das statische SQL. So wird genannt, weil es sich nicht ändert. Sobald es geschrieben ist, bedeutet das, dass es in Stein gehämmert ist.
Unten ist ein Beispiel für ein statisches SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
Haben Sie bemerkt, dass es hier zwei Anweisungen gibt? Jede Anweisung gibt eine Zusammenfassung der JobTitles für einen bestimmten Mitarbeiter Geburtsjahr. Wenn wir weitere Geburtsjahre hinzufügen möchten, müssen wir weitere Aussagen hinzufügen., Was wäre, wenn wir die Erklärung nur einmal schreiben müssten und das Jahr on-the-fly ändern könnten?
Hier kommt das dynamische SQL ins Spiel.
Dynamic SQL ist SQL, das zur Laufzeit erstellt und ausgeführt wird. Es klingt kompliziert, aber es ist wirklich nicht. Anstatt den Anweisungen eingegeben, die direkt in der gespeicherten Prozedur, die SQL-Anweisungen werden zunächst erstellt und definiert in Variablen.
Der code in diesen Variablen wird dann ausgeführt. Fahren wir nun mit unserem Beispiel fort, hier ist derselbe Code, der das dynamische SQL verwendet:
Das dynamische SQL ist grün hervorgehoben., Dies ist die SQL, die für jedes @birthYear erstellt wird. Während die SQL erstellt wird, wird sie in @anweisung gespeichert. Es wird dann mit sp_executesql ausgeführt, was wir unten erklären werden.
Einführung in sp_executesql
Mit sp_executeslq können Sie das in einer Variablen gespeicherte transact SQL ausführen. Das Anweisungsformular lautet
EXECUTE sp_executesql @statement.
Falls Sie sich fragen, ist sp_executesql eine im System gespeicherte Prozedur. Das system gespeicherte Prozeduren erweitern, die Sprache und bieten mehr Funktionen für Sie zu verwenden.,
Hier ist ein einfaches Beispiel zum Ausprobieren:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Wenn Sie dies im Abfragefenster ausführen, erhalten Sie ein ähnliches Ergebnis wie dieses:
2018-01-24 18:49:30.143
Nachdem Sie nun gesehen haben, wie sp_executeslq funktioniert, üben wir es aus. Nehmen wir an, Sie wurden gebeten, eine Speicherprozedur zu schreiben, die entweder die durchschnittliche Liniensumme oder die Summe von Liniensumme nach ProductID für Produkte zurückgibt, die 2011 ausgeliefert wurden.
Ihr Chef würde es vorziehen, dies als gespeicherte Prozedur schreiben zu lassen. Die gespeicherte Prozedur sollte einen Parameter @ReturnAverage akzeptieren., Wenn true, geben Sie den Durchschnitt zurück, andernfalls die Summe.
Natürlich könnten Sie dies als zwei separate Abfragen schreiben, wie im folgenden gespeicherten Prozess gezeigt, aber das würde nicht viel Spaß machen, da es zu viel Tippen und fehleranfällig wäre!
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
Der schlechte Teil hier ist, dass es viel doppelten Code gibt, den ich grün gefärbt habe. Es gibt nicht viel eindeutigen Code, aber das gibt es, ist rot gefärbt.
Mit all dieser Redundanz haben wir eine großartige Gelegenheit, dynamisches SQL zu zeigen. Lass es uns machen!,
Anstatt zwei vollständige Versionen des SQL zu haben, eine für AVG, die andere für SUM, erstellen wir die angeforderte Version im laufenden Betrieb.
Die SQL wird erstellt und in der Variablen @Anweisung gespeichert. Diese Variable wird basierend auf dem Parameterwert @returnAverage erstellt. Wenn auf 1 gesetzt, dann die @ – Funktion stellt den Mittelwert; sonst, Summierung.
Sie können sehen, wo die SQL dann gebaut wird, um Anweisung zu erstellen. Beachten Sie die Farbcodierung. Es sollte ähnliche Teile innerhalb der statischen Version entsprechen; Dies sollte Ihnen helfen, einen Vergleich durchzuführen.,
Debuggen von dynamischem SQL
Sie fragen sich vielleicht, wie das SQL zur Laufzeit aussieht. Sie können den Code einfach mit dem Debugger überprüfen:
Führen Sie die gespeicherte Prozedur mit dem Befehl Ausführen des Debuggers aus und treten Sie dann in den Code ein
Fahren Sie mit dem Code fort, bis Sie die unten hervorgehobene Execute-Anweisung gelesen haben.
Wenn Sie diese Anweisung erreicht haben, bewegen Sie den Mauszeiger über die @ – Anweisung, und wenn die Werkzeugspitze angezeigt wird, wählen Sie text Visualizer.,
Der Debugger ist leistungsstark und verständnisvoll. Wenn würde Sie sehr ermutigen, hier mehr darüber zu erfahren.
Mit sp_executesql mit Parametern
können Sie mit sp_executesql auf Parameter in Ihrer Anweisung verweisen. Dies erleichtert letztendlich das Lesen Ihres Codes und bietet einige Optimierungsvorteile, da die Anweisung einmal kompiliert und viele Male wiederverwendet werden kann.
Die Anweisung hat die Form:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Also lasst uns die Stücke erklären.
- – @ – Anweisung ist die SQL, die wir ausführen möchten.,
- @parameterDefinition ist eine Zeichenfolge, die eine Definition aller Parameter enthält, auf die in der @Anweisung verwiesen wird. Jeder Parameter und Typ found @statement wird aufgelistet. Name und Typ sind durch ein Leerzeichen getrennt. Mehrere Parameter werden durch ein Komma getrennt.
Als nächstes legen wir die Parameterwerte fest, indem wir die Parameter und den gewünschten Wert angeben. Die Parameter werden in der Reihenfolge aufgelistet, die in der Zeichenfolge @parameterDefinition definiert ist.
- @parm1 ist der erste Parameter, der in der Zeichenfolge @parameterDefinition definiert ist. Wert ist der Wert, auf den Sie ihn einstellen möchten.,
- @parm2, ist der zweite Parameter, wenn definiert, wie in @parameterDefinition deklariert.
- und so weiter…
Hier ist ein einfaches Beispiel, das zwei Zahlen hinzufügt, um zu versuchen:
Die verschiedenen Teile der Anweisung sind farbcodiert:
- @Anweisung (grün) – Beachten Sie, dass sie 2 Parameter enthält: @a und @b. Beachten Sie auch, dass diese nicht in TSQL deklariert sind. Vielmehr sind sie in der Parameterdefinition definiert.
- @parameterDefinition (blau) – jeder aufgelistete Parameter ist als Typ int definiert.
- Parameterwerte (rot) – hier legen wir den Wert der Parameter fest.,
Zum Abschluss haben wir in diesem Beispiel eine dynamisch ausgeführte SQL-Anweisung, die zwei Parameter hinzufügt.
Diese Parameter sind als Ganzzahlen definiert. Der Wert jedes Parameters wird im Befehl sp_executesql festgelegt.
Beispiel mit sp_executesql mit Parametern
Nehmen wir unser vorheriges Beispiel und erweitern es. Anstatt das shipDate wie gewohnt in die Abfrage fest zu codieren, lassen Sie uns dies als Parameter einbringen. Dies macht die Abfrage flexibler und arbeitet mit anderen Jahren als 2011.,
Um diese Änderung vorzunehmen, fügen wir unserer gespeicherten Prozedur sowie der dynamischen Abfrage einen Parameter hinzu. Wir verwenden den Befehl sp_executesql, um die dynamische Abfrage mit diesen Parametern aufzurufen.
Die aktualisierte gespeicherte Prozedur mit änderungen ist unten dargestellt. Der Parameter stored procedure ist grün und der Parameter dynamic query rot.
Um dies auszuführen, rufen Sie einfach den uspCalculateSalesSummaryDynamic2 proc aus einem Abfragefenster mit dem folgenden Befehl auf:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Wenn Sie dies tun, sehen Sie diese Ergebnisse.,
Lassen sie mich zeigen sie eine feine vereinfachung, lassen sie uns kombinieren @shipDateYear und @shipDate in einem parameter. Wir werden @shipDateYear aus unserem Code entfernen. Dies erleichtert das Folgen und Lesen:
Beachten Sie, dass die EXECUTE-Anweisung viel einfacher ist, es ist nicht erforderlich, den SQL-Anweisungsparameter @shipDateYear dem Wert des Speicherprozedurparameters @shipDate zuzuweisen.
Dadurch wird die Aussage kompakter und leichter lesbar., Der Ablauf scheint besser zu lesen, da Sie keine Verbindungen zwischen den gespeicherten Prozedurparametern und den SQL-Parametern herstellen müssen
Führen Sie dynamisches SQL mit EXECUTE()
Sie können auch den Befehl EXEC oder EXECUTE verwenden, um dynamisches SQL auszuführen. Das Format für diesen Befehl lautet
EXECUTE (@statement)
Hier ist ein einfaches Beispiel zum Ausprobieren:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
Es ist wichtig, @Anweisung in Klammern einzuschließen. Wenn Sie nicht die EXECUTE Anweisung nimmt @Anweisung, und anstatt die dynamische SQL ausgeführt wird, denkt es, dass der Variablenwert der Name einer gespeicherten Prozedur ist., Sie erhalten den folgenden Fehler:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
Dies bietet natürlich einen großartigen Hinweis! Wenn Sie möchten, können Sie Variablen verwenden, um anzugeben, welche gespeicherten Prozeduren aufgerufen werden sollen.
sp_executesql versus EXECUTE
Sie fragen sich vielleicht, warum sp_executesql versus EXECUTE. Was ist der Unterschied zwischen den beiden?
Aus mehreren Gründen empfiehlt Microsoft die Verwendung von sp_executesql zum Ausführen von dynamischem SQL:
- Mit EXECUTE können alle Parameter von ihrem nativen Typ in Unicode konvertiert werden., Dies behindert die Fähigkeit des Optimierers, das dynamisch erstellte SQL mit einem bereits vorhandenen Plan abzugleichen.
- Mithilfe von sp_executesql erkennt der Optimierer die Parameter innerhalb des dynamischen SQL und erleichtert dem Optimierer die Übereinstimmung mit Plänen.
- Es ist einfacher, parametrisierte Abfragen zu lesen, als eine Reihe von verkettetem Text zu lesen, der sie enthält.
- Parametrisierte Abfragen sind weniger anfällig für SQL-injection-Angriffe.