この記事を読んだ後で、動的SQLの基本、変数値に基づいてステートメントを構築する方法、およびsp_executesqlおよびEXECUTE()をストアドプロシージャ内から使用して構築されたステートメントを実行する方法について学びます。
このレッスンのすべての例は、Microsoft SQL Server Management StudioおよびAdventureWorksおよびWideworldimporterのサンプルデータベースに基づいています。 あなたは私のガイドを使用して、これらの無料のツールを使用して始めることができます。,
ストアドプロシージャで動的SQLをビルドします。
私たちが書いた多くのSQLは、明示的にストアドプロシージャに書き込まれます。 これが静的SQLと呼ばれるものです。 このような名前は変更されないためです。 それが書かれたら、それはそれが石にセット打たれていることを意味します。
以下は静的SQLの例です。
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
ここに二つのステートメントがあることに気付きましたか？ 各ステートメントは、特定の従業員の誕生年のJobTitlesの概要を返します。 我々はより多くの出生年を追加したい場合は、我々はより多くの文を追加する必要があります。, 私たちは一度だけ文を書いて、オンザフライ年を変更することができなければならなかった場合はどうなりますか？
これが動的SQLの出番です。動的SQLは、実行時に作成および実行されるSQLです。 ストアドプロシージャに直接入力されたステートメントを持つ代わりに、SQLステートメントは最初に変数で構築および定義されます。
これらの変数のコードが実行されます。 ここでは、動的SQLを使用した同じコードを示します。
動的SQLは緑色で強調表示されます。, これは、@birthYearごとに構築されるSQLです。 SQLがビルドされると、@statementに格納されます。 その後、sp_executesqlを使用して実行されます。
sp_executesqlの概要
sp_executeslqを使用して、変数内に格納されているtransact SQLを実行できます。 ステートメント形式は
EXECUTE sp_executesql @statement.
あなたが疑問に思っている場合、sp_executesqlはシステムストアドプロシージャです。 システムの保存の手続きの言語を特徴だ。,
試してみる簡単な例は次のとおりです。
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
これをクエリウィンドウで実行すると、次のような結果が得られます。
2018-01-24 18:49:30.143
sp_executeslqがどのように機能するかを見たので、実践してみましょう。 2011年に出荷された製品のProductIDによる平均LineTotalまたはLineTotalの合計のいずれかを返すstoreプロシージャを記述するように求められているとしましょう。
あなたの上司は、これをストアドプロシージャとして書くことを好むでしょう。 の保存の手順を受け入れるべき一つのパラメータ@ReturnAverage., Trueの場合、平均を返し、それ以外の場合は合計を返します。
もちろん、次の格納されたprocに示すように、これを二つの別々のクエリとして書くことができますが、それはあまりにも多くの入力とエラーが発生しやす
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
ここで悪い部分は、私が緑色に着色した重複したコードがたくさんあることです。 あまりユニークなコードはありませんが、そこには赤い色が付いています。
このすべての冗長性で、我々はいくつかの動的SQLを披露する絶好の機会を持っています。 それでは、それのために行こう！,
ここでは、AVG用のsqlの完全なバージョンとSUM用のSQLの完全なバージョンの代わりに、要求されたバージョンをオンザフライでビルドします。
SQLはビルドされ、変数@statementに保存されます。 この変数は、パラメーター値@returnAverageに基づいて構築されます。 1に設定されている場合、@functionは平均を表し、それ以外の場合は合計を表します。
sqlがcreateステートメントにビルドされている場所を見ることができます。 色分けに注意してください。 これは、静的バージョン内の同様の部分に対応する必要があります。,
動的SQLのデバッグ
実行時にSQLがどのように見えるか疑問に思うかもしれません。 デバッガーを使用してコードを簡単に調べることができます。
デバッガーのrunコマンドを使用してストアドプロシージャを実行し、コードにステップインします。
このステートメントに到達したら、@ステートメントの上にカーソルを置き、ツールヒントが表示されたら、テキストビジュアライザを選択します。,
デバッガは強力で理解する価値があります。 非常にあなたがここでそれについての詳細を学ぶ奨励する場合。
sp_executesqlとパラメーターを使用する
sp_executesqlを使用して、ステートメント内のパラメーターを参照できます。 これが最終的にコードを読みやすくなり、一部の最適化の特典としての計算書き一度作成され再利用されました。
ステートメントは次の形式をとります。
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
それでは、作品を説明しましょう。
- @statementは、実行するSQLです。,
- @parameterDefinitionは、@statementで参照されるすべてのパラメータの定義を含む文字列です。 各パラメータとタイプfound@statementが一覧表示されます。 名前とタイプはスペースで区切られます。 複数のパラメータはコンマで区切られます。
次に、パラメータと目的の値を指定することによって、パラメータ値を設定します。 パラメーターは、@parameterDefinition文字列内で定義されている順序で一覧表示されます。
- @parm1は、@parameterDefinition文字列内で定義されている最初のパラメーターです。 値は値で、設定したい値です。,
- @parm2は、@parameterDefinitionで宣言されているように、定義されている場合、第二のパラメータです。ステートメントのさまざまな部分は色分けされています。
- @statement(green)–@aと@bの2つのパラメータが含まれていることに注意してください。 むしろ、それらはパラメータ定義で定義されています。
- @parameterDefinition(blue)-リストされている各パラメーターはint型として定義されています。
- パラメータ値（赤）–ここでパラメータの値を設定します。,
最後に、この例では、二つのパラメータを追加する動的に実行されたSQLステートメントがあります。
これらのパラメータは整数として定義されます。 各パラメーターの値は、sp_executesqlコマンドで設定されます。
パラメータ付きsp_executesqlを使用した例
前の例を見て、それを拡張しましょう。 ShipDateをクエリにハードコーディングするのではなく、それをパラメータとして取り込みましょう。 これにより、クエリはより柔軟になり、2011年以外の年で動作します。,
この変更を行うには、ストアドプロシージャにパラメーターと動的クエリを追加します。 これらのパラメーターを使用して動的クエリを呼び出すには、sp_executesqlコマンドを使用します。
更新されたストアドプロシージャの変更を以下に示します。 のストアドプロシージャパラメータが緑色、および、動的にクエリパラメータ。
これを実行するには、次のコマンドを使用して、クエリウィンドウからuspCalculateSalesSummaryDynamic2procを呼び出すだけです。
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
そうすると、これらの結果が表示されます。,
クエリ結果 私はあなたに一つの細かい単純化を示してみましょう、のは、一つのパラメータに@shipDateYearと@shipDateを組み合わせてみましょう。 コードから@shipDateYearを削除します。 これにより、フォローと読み取りが容易になります。
EXECUTEステートメントははるかに簡単であるため、sqlステートメントパラメータ@shipDateYearをstoreプロシージャパラメータ@shipDateの値に割り当てる必要はありません。
これにより、ステートメントがよりコンパクトで読みやすくなります。, ストアドプロシージャパラメータとSQLパラメータとの間に精神的に接続する必要がないため、フローはより良く読まれるようです。
EXECUTE Dynamic SQL with EXECUTE()
EXECまたはEXECUTEコマンドを使用して動的SQLを実行することもできます。 このコマンドの形式は次のとおりです。
EXECUTE (@statement)
試してみる簡単な例は次のとおりです。
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
@statementを括弧で囲むことが重要です。 EXECUTEステートメントが@statementを使用しない場合、動的SQLを実行する代わりに、変数値がストアドプロシージャの名前であると見なされます。, あなたは次のエラーが発生します：
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
もちろん、これは素晴らしいヒントを提供します！ 必要に応じて、変数を使用して、呼び出すストアドプロシージャを指定できます。
sp_executesql vs EXECUTE
なぜsp_executesql vs EXECUTEを使用するのか疑問に思うかもしれません。 両者の違いは何ですか？
Microsoftが動的SQLを実行するためにsp_executesqlを使用することを推奨するいくつかの理由は次のとおりです。
- すべてのパラメータを実行すると、ネイティブ型からUnicodeに変換されることが多くあります。, これにより、動的に構築されたSQLと既存の計画を一致させるオプティマイザーの機能が妨げられます。
- sp_executesqlを使用すると、オプティマイザーは動的SQL内のパラメーターを認識するため、オプティマイザーがプランを簡単に一致させることができます。
- パラメータ化されたクエリを読み取る方が、それらを組み込んだ連結されたテキストの束を読み取るよりも簡単です。
- パラメータ化された問合せくいSQLインジェクション攻撃であった。