Po przeczytaniu tego artykułu dowiesz się podstaw dynamicznego SQL, jak budować instrukcje oparte na wartościach zmiennych oraz jak wykonywać skonstruowane instrukcje za pomocą sp_executesql i EXECUTE() z poziomu procedury składowanej.
wszystkie przykłady zawarte w tej lekcji są oparte na Microsoft SQL Server Management Studio i przykładowych bazach danych z AdventureWorks i WideWorldImporters. Możesz zacząć korzystać z tych bezpłatnych narzędzi za pomocą mojego przewodnika, Pierwsze kroki za pomocą SQL Server.,
Zbuduj dynamiczny SQL w procedurze składowanej.
wiele zapisywanych przez nas SQL jest jawnie zapisywanych do procedury składowanej. To właśnie nazywamy statycznym SQL. Takie jest nazwane, ponieważ się nie zmienia. Kiedy jest napisane, oznacza to, że jest wmurowane w kamień.
Poniżej znajduje się przykład statycznego SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
Czy zauważyłeś, że są tu dwa stwierdzenia? Każde oświadczenie zwraca podsumowanie tytułów pracy dla określonego roku urodzenia pracownika. Jeśli chcemy dodać więcej lat urodzenia, musimy dodać więcej stwierdzeń., Co by było, gdybyśmy musieli napisać oświadczenie tylko raz i móc zmienić rok w locie?
tutaj pojawia się dynamiczny SQL.
Dynamic SQL to SQL, który jest tworzony i wykonywany w czasie wykonywania. Brzmi to skomplikowanie, ale tak naprawdę nie jest. zamiast instrukcji wpisywanych bezpośrednio do procedury składowanej, polecenia SQL są najpierw budowane i definiowane w zmiennych.
kod tych zmiennych jest następnie wykonywany. Teraz, kontynuujmy nasz przykład, tutaj jest ten sam kod za pomocą dynamicznego SQL:
dynamiczny SQL jest podświetlony na Zielono., Jest to SQL, który jest zbudowany dla każdego @birthYear. Gdy SQL jest budowany, jest przechowywany w @ statement. Jest on następnie wykonywany za pomocą sp_executesql, co wyjaśnimy poniżej.
Wprowadzenie do sp_executesql
możesz użyć sp_executeslq do wykonania transact SQL przechowywanego w zmiennej. Forma instrukcji to
EXECUTE sp_executesql @statement.
w przypadku, gdy zastanawiasz się, sp_executesql jest procedurą składowaną systemu. Systemowe procedury składowane rozszerzają język i zapewniają więcej funkcji do użycia.,
Oto prosty przykład do wypróbowania:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
jeśli uruchomisz to w oknie zapytania, uzyskasz podobny wynik:
2018-01-24 18:49:30.143
teraz, gdy widzisz, jak działa sp_executeslq, przećwiczmy to. Załóżmy, że zostałeś poproszony o napisanie procedury sklepu, która zwraca średnią LineTotal lub sumę LineTotal by ProductID dla produktów wysłanych w 2011 roku.
twój szef wolałby, aby to zostało zapisane jako procedura składowana. Procedura składowana powinna przyjmować jeden parametr @ReturnAverage., Jeśli true, to zwrócisz średnią, w przeciwnym razie sumę.
oczywiście, możesz napisać to jako dwa oddzielne zapytania, jak pokazano w poniższym zapisanym proc, ale to nie byłoby zbyt zabawne, ponieważ byłoby to zbyt dużo pisania i podatne na błędy!
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
zła część jest tutaj jest dużo duplikatów kodu, który pokolorowałem na Zielono. Nie ma wiele unikalny kod, ale to jest, jest w kolorze czerwonym.
z całą tą redundancją, mamy świetną okazję pochwalić się dynamicznym SQL. Do dzieła!,
tutaj, zamiast mieć dwie pełne wersje SQL, jedną dla AVG, drugą dla SUM, budujemy żądaną wersję w locie.
SQL jest wbudowany i zapisywany do zmiennej @statement. Ta zmienna jest zbudowana na podstawie wartości parametru @returnAverage. Jeśli jest ustawione na 1, to @ function reprezentuje średnią; w przeciwnym razie sumowanie.
możesz zobaczyć, gdzie jest następnie zbudowany SQL, aby utworzyć instrukcję. Zwróć uwagę na kodowanie kolorów. Powinno to odpowiadać podobnym fragmentom w wersji statycznej; powinno to pomóc w dokonaniu porównania.,
debugowanie dynamicznego SQL
możesz się zastanawiać, jak wygląda SQL w czasie wykonywania. Możesz łatwo sprawdzić kod za pomocą debuggera:
uruchom procedurę składowaną za pomocą polecenia run, a następnie przejdź do kodu
Kontynuuj wchodzenie do kodu, dopóki nie przeczytasz instrukcji Execute podświetlonej poniżej.
Po osiągnięciu tego polecenia najedź kursorem na instrukcję@, a gdy zostanie wyświetlona wskazówka, wybierz wizualizator tekstu.,
debugger jest potężny i wart zrozumienia. Jeśli gorąco zachęcamy, dowiedz się więcej na ten temat tutaj.
używając sp_executesql z parametrami
Możesz użyć sp_executesql do odniesienia się do parametrów w instrukcji. To ostatecznie sprawia, że kod jest łatwiejszy do odczytania i zapewnia pewne korzyści optymalizacyjne, ponieważ instrukcja może być skompilowana raz i ponownie wykorzystana wiele razy.
oświadczenie ma postać:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
więc wyjaśnijmy te fragmenty.
- @statement jest SQL, który chcemy wykonać.,
- @ parameterDefinition jest łańcuchem znaków zawierającym definicję wszystkich parametrów, do których odwołuje się instrukcja@. Każdy znaleziony parametr i typ instrukcji @jest wyświetlany. Nazwa i typ są oddzielone spacją. Wiele parametrów jest oddzielonych przecinkiem.
następnie ustawiamy wartości parametrów, określając parametry i żądaną wartość. Parametry są wymienione w kolejności określonej w łańcuchu @ parameterDefinition.
- @parm1 jest pierwszym parametrem zdefiniowanym w łańcuchu @parameterDefinition. Wartość jest wartością, na którą chcesz ją ustawić.,
- @parm2, jest drugim parametrem, jeśli definiuje, zadeklarowanym w parametrze @ parameterDefinition.
- i tak dalej…
Oto prosty przykład, który dodaje dwie liczby, aby spróbować:
różne części instrukcji są oznaczone kolorami:
- @statement (zielony) – zauważ, że zawiera 2 parametry: @a i @b. zauważ również, że nie są one zadeklarowane w TSQL. Są one raczej zdefiniowane w definicji parametru.
- @ parameterDefinition – blue) – każdy z podanych parametrów jest zdefiniowany jako typ int.
- wartości parametrów (czerwony) – tutaj ustawiamy wartość parametrów.,
aby zakończyć, w tym przykładzie mamy dynamicznie wykonywaną instrukcję SQL, która dodaje dwa parametry.
parametry te są zdefiniowane jako liczby całkowite. Wartość każdego parametru jest ustawiana w Komendzie sp_executesql.
przykład użycie sp_executesql z parametrami
weźmy nasz poprzedni przykład i rozszerzmy go. Zamiast twardego kodowania shipDate w zapytaniu, tak jak to zrobiliśmy, przyjmijmy to jako parametr. To sprawia, że zapytanie jest bardziej elastyczne i działa z latami innymi niż 2011.,
aby dokonać tej zmiany, dodamy parametr do naszej procedury składowanej, a także dynamiczne zapytanie. Użyjemy polecenia sp_executesql do wywołania dynamicznego zapytania przy użyciu tych parametrów.
zaktualizowana procedura składowana ze zmianami jest pokazana poniżej. Parametr procedury składowanej jest zielony, a parametr dynamic query czerwony.
aby to uruchomić, po prostu wywołaj proc uspCalculateSalesSummaryDynamic2 z okna zapytania, używając następującego polecenia:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Jeśli to zrobisz, zobaczysz te wyniki.,
pozwól, że pokażę Ci jedno drobne uproszczenie, połączmy @shipdateear i @shipDate w jeden parametr. Wyeliminujemy @shipDateYear z naszego kodu. Ułatwia to śledzenie i czytanie:
zauważ, że instrukcja EXECUTE jest znacznie prostsza, nie ma potrzeby przypisywania parametru polecenia SQL @shipdateeyear do parametru procedury store @ shipdate ' s value.
To sprawia, że oświadczenie jest bardziej kompaktowe i łatwiejsze do odczytania., Przepływ wydaje się odczytywać lepiej, ponieważ nie musisz mentalnie tworzyć połączeń między parametrami procedury składowanej a parametrami SQL
Uruchom dynamiczny SQL za pomocą EXECUTE()
Możesz również użyć polecenia EXEC lub EXECUTE do uruchomienia dynamicznego SQL. Format tego polecenia to
EXECUTE (@statement)
Oto prosty przykład do wypróbowania:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
ważne jest, aby załączyć @statement w nawiasie. Jeśli polecenie EXECUTE nie przyjmuje polecenia @i zamiast uruchamiać dynamiczny SQL, uważa, że wartość zmiennej jest nazwą procedury składowanej., Otrzymasz następujący błąd:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
oczywiście, zapewnia to świetną podpowiedź! Jeśli chcesz, możesz użyć zmiennych, aby określić, które procedury składowane mają być wywołane.
sp_executesql versus EXECUTE
możesz się zastanawiać po co używać sp_executesql versus EXECUTE. Jaka jest różnica między nimi?
Oto kilka powodów, dla których Microsoft zaleca używanie sp_executesql do uruchamiania dynamicznego SQL:
- z wykonaniem wszystkich parametrów dużo być konwertowane z ich natywnego typu do Unicode., Utrudnia to zdolność optymalizatora do dopasowania dynamicznie zbudowanego SQL z wcześniej istniejącym planem.
- używając sp_executesql, optymalizator rozpoznaje parametry w dynamicznym SQL, ułatwiając optymalizatorowi dopasowanie planów.
- łatwiej jest odczytywać zapytania sparametryzowane niż kilka skonkatenowanych tekstów, które je zawierają.
- zapytania parametryzowane są mniej podatne na ataki SQL injection.