Poté, co jste si přečetli tento článek, budete mít možnost naučit se základy dynamického SQL, jak vytvořit prohlášení na základě hodnot proměnných, a jak provádět konstruovány prohlášení pomocí sp_executesql a EXECUTE() v rámci uložené procedury.
Všechny příklady nalezené v této lekci jsou založeny na Microsoft SQL Server Management Studio a ukázkové databáze AdventureWorks a WideWorldImporters. Můžete začít používat tyto bezplatné nástroje pomocí mého průvodce, Jak začít používat SQL Server.,
Vytvořte dynamický SQL v uloženém postupu.
mnoho SQL, které píšeme, je explicitně zapsáno do uloženého postupu. Tomu říkáme statický SQL. Takový je pojmenován, protože se nemění. Jakmile je to napsáno, znamená to, že je zasazeno do kamene.
níže je příklad statického SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
všimli jste si, že zde existují dvě prohlášení? Každé prohlášení vrací souhrn pracovních míst pro konkrétní rok narození zaměstnance. Pokud chceme přidat další roky narození, musíme přidat další prohlášení., Co kdybychom museli napsat prohlášení pouze jednou a mohli změnit rok za letu?
zde přichází do hry dynamický SQL.
Dynamic SQL je SQL, který je vytvořen a proveden za běhu. To zní složité, ale to opravdu není. Místo toho, aby příkazy zadali přímo do uložené procedury, SQL příkazy jsou první postavený a definované v proměnné.
pak se provede kód v těchto proměnných. Nyní pokračujeme v našem příkladu, zde je stejný kód pomocí dynamického SQL:
dynamický SQL je zvýrazněn zeleně., Toto je SQL, který je vytvořen pro každý @ birthYear. Jak je SQL postaven, je uložen v příkazu@. Poté se provede pomocí sp_executesql, což vysvětlíme níže.
Úvod do sp_executesql
sp_executeslq můžete použít k provedení transakce SQL uložené v proměnné. Formulář prohlášení je
EXECUTE sp_executesql @statement.
v případě, že vás zajímá, sp_executesql je systémový uložený postup. Postupy uložené v systému rozšiřují jazyk a poskytují vám další funkce.,
Zde je jednoduchý příklad, aby se pokusili:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Pokud jste to udělat v okně dotazu, dostanete podobný výsledek, jako je tento:
2018-01-24 18:49:30.143
Nyní, když jste viděli, jak sp_executeslq funguje, pojďme dát to do praxe. Předpokládejme, že jste byli požádáni, aby napsali, obchod, postup, který vrátí buď průměrná Linecelkem nebo součet Linecelkem o ProductID pro výrobky dodávané v roce 2011.
váš šéf by to raději nechal napsat jako uložený postup. Uložený postup by měl přijmout jeden parametr @ReturnAverage., Pokud je to pravda, pak vrátíte průměr, jinak součet.
samozřejmě, mohu to napsat, jako dva samostatné dotazy, jak je znázorněno v následující uložené proc ale to by nebylo moc zábavné, protože to by bylo moc psaní a náchylné k chybám.
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
špatná část je zde spousta duplicitního kódu, který jsem zbarvil zeleně. Tam není moc jedinečný kód, ale že tam je, je červená barva.
s touto redundancí máme skvělou příležitost předvést nějaký dynamický SQL. Jdeme na to!,
zde místo dvou kompletních verzí SQL, jedné pro AVG, druhé pro SUM, sestavíme požadovanou verzi on-the-fly.
SQL je zabudován a uložen do proměnné @ statement. Tato proměnná je postavena na základě hodnoty parametru @returnAverage. Pokud je nastavena na hodnotu 1, pak funkce @představuje průměr; jinak součet.
můžete vidět, kde je SQL vytvořen pro vytvoření příkazu. Všimněte si barevného kódování. Mělo by odpovídat podobným částem v rámci statické verze; To by vám mělo pomoci provést srovnání.,
ladění dynamického SQL
možná se divíte, jak SQL vypadá v době běhu. Můžete snadno zkontrolovat kód pomocí debugger:
Spustit uložené procedury pomocí ladicího programu spustit příkaz, a pak Krok Do kódu
Pokračujte Krokem Do kódu, dokud si nepřečtete Provést prohlášení je zdůrazněno níže.
Jakmile se dostanete na toto prohlášení, vznášet se nad @prohlášení, a když špička nástroje se zobrazí, vyberte text visualizer.,
debugger je silný a stojí za pochopení. Pokud by velmi povzbudit se dozvíte více o tom zde.
pomocí sp_executesql s parametry
můžete použít sp_executesql pro referenční parametry v rámci vašeho příkazu. To v konečném důsledku usnadňuje čtení kódu a poskytuje některé optimalizační výhody, protože prohlášení lze sestavit jednou a mnohokrát znovu použít.
příkaz má podobu:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
takže pojďme vysvětlit kousky.
- @příkaz je SQL, který chceme spustit.,
- @ parametrdefinition je řetězec obsahující definici všech parametrů odkazovaných v příkazu@. Každý parametr a typ nalezeného příkazu @je uveden. Název a typ jsou odděleny mezerou. Více parametrů je odděleno čárkou.
dále nastavíme hodnoty parametrů zadáním parametrů a požadované hodnoty. Parametry jsou uvedeny v pořadí definovaném v řetězci @ parametrdefinice.
- @parm1 je první parametr definovaný v řetězci @ parameterDefinition. Hodnota je hodnota, kterou chcete nastavit.,
- @parm2, je druhý parametr, pokud definuje, jak je deklarováno v @parametrdefinition.
- a tak dále…
Zde je jednoduchý příklad, který přidat dvě čísla, aby se pokusili:
různé části výkazu jsou barevně odlišeny:
- @prohlášení (zelená) – oznámení obsahuje 2 parametry: @a a @b. Všimněte si také, tyto nejsou deklarovány v TSQL. Spíše jsou definovány v definici parametrů.
- @ parametrdefinice (modrá) – každý uvedený parametr je definován jako typ int.
- hodnoty parametrů (červená) – zde nastavíme hodnotu parametrů.,
Chcete-li zabalit, v tomto příkladu máme dynamicky provedený příkaz SQL, který přidává dva parametry.
tyto parametry jsou definovány jako celá čísla. Hodnota každého parametru je nastavena v příkazu sp_executesql.
Příklad použití sp_executesql s Parametry
vezměme si náš předchozí příklad a rozšířit ji. Spíše než hardcoding shipDate do dotazu, jak jsme to udělali, pojďme přinést to jako parametr. Díky tomu je dotaz flexibilnější a pracuje s jinými roky než 2011.,
Chcete-li provést tuto změnu, přidáme parametr do našeho uloženého postupu, stejně jako dynamický dotaz. Pomocí příkazu sp_executesql zavoláme dynamický dotaz pomocí těchto parametrů.
aktualizovaný uložený postup se změnami je uveden níže. Parametr uložené procedury je zelený a parametr dynamického dotazu červený.
spustit tento jednoduše zavolejte uspCalculateSalesSummaryDynamic2 proc z dotazu systému windows pomocí následujícího příkazu:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Pokud tak učiníte, budete vidět tyto výsledky.,
Dovolte mi vám ukázat jeden pěkný zjednodušení, řekněme, kombinovat @shipDateYear a @shipDate do jednoho parametru. Odstraníme @shipDateYear z našeho kódu. Díky tomu je snazší sledovat a číst:
Všimněte si, že příkaz PROVEDENÍ je mnohem jednodušší, není třeba přiřadit parametru SQL příkazu @shipDateYear do obchodu postupu parametr @shipDate hodnotu.
díky tomu je prohlášení kompaktnější a snadněji čitelné., Průtok se zdá lépe číst, tak nemusíte, aby mentálně vytvořit spojení mezi parametry uložené procedury a SQL parametry
Spustit Dynamické SQL EXECUTE()
můžete také použít EXEC nebo VYKONAT příkaz spustit dynamické SQL. Formát tohoto příkazu je,
EXECUTE (@statement)
Zde je jednoduchý příklad, aby se pokusili:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
je důležité přiložit @prohlášení v závorkách. Pokud příkaz EXECUTE neučiníte takes @ statement a namísto spuštění dynamického SQL si myslí, že hodnota proměnné je název uložené procedury., Získáte následující chybu:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
to samozřejmě poskytuje skvělý tip! Pokud si přejete, můžete pomocí proměnných určit, které uložené postupy chcete volat.
sp_executesql versus EXECUTE
možná vás zajímá, Proč používat sp_executesql versus EXECUTE. Jaký je rozdíl mezi těmito dvěma?
Zde je několik důvodů, proč společnost Microsoft doporučuje pomocí sp_executesql spustit dynamické SQL:
- PROVÁDĚT všechny parametry musí být přepočteny z jejich nativní typ Unicode., To brzdí schopnost optimalizátoru odpovídat dynamicky postavenému SQL s již existujícím plánem.
- pomocí sp_executesql optimalizátor rozpozná parametry v dynamickém SQL, což usnadňuje optimalizátor tak, aby odpovídal plánům.
- je snazší číst parametrizované dotazy, než je číst spoustu zřetězeného textu, který je obsahuje.
- parametrizované dotazy jsou méně náchylné k útokům SQL injection.