1 コンテキスト
IDENTITY プロパティの指定されたテーブルに行を挿入し、その際、同時に生成された ID 列の値を取得したい。
また、プリセット値など、ID 列の値は指定できるようにしたい。
2 コード
2.1 Entities テーブル
2.2 usp_BasicInsertEntities ストアドプロシージャ
2.2 usp_AdvancedInsertEntities ストアドプロシージャ
2.3 DropObjects スクリプト
3 要素と概念
3.1 IDENTITY プロパティ、ID 列
テーブルに ID 列を作成します。
ID 列はキー値の生成に使用できます。列の ID プロパティでは、次の点が保証されます。
行を挿入するときは、列リストおよび VALUES セクションから ID 列を省く必要があります（唯一の例外は、IDENTITIY_INSERT オプションがオンの場合です）。 列リストを提供する場合は、値が自動的に提供される列を省く必要があります。
「6.4 IDENTITY プロパティ」p252 *1
3.2 SCOPE_IDENTITY
ID 列を扱う際に便利な関数が他に 2 つあります。SCOPE_IDENTITY と IDENT_CURRENT です。 SCOPE_IDENTITY は、同一スコープ内のテーブルに挿入された最後の ID 値を返し、これはストアドプロシージャ、トリガ、またはバッチの可能性があります。
「6.4 IDENTITY プロパティ」p253 *1
スコープはモジュール、つまりストアドプロシージャ、トリガー、関数、またはバッチです。 したがって、2 つのステートメントが同じストアドプロシージャ、関数、またはバッチにある場合、これらのステートメントのスコープは同じになります。
「SCOPE_IDENTITY (Transact-SQL)」
・モジュールは、（アプリケーションプログラムなど）「コンパイル可能な単位」によって実行可能な SQL ステートメントで構成される。 概念上は、バインディング方式の違いにかかわらず、SQL ステートメントは必ずモジュールに含まれていると見なされる*1。
*1 WHENEVER、BEGIN、END DECLARE SECTION など、埋め込み SQL だけで使用される実行不能なステートメントや、CREATE SCHEMA ステートメントなど、特定の「スタンドアロン」ステートメントを除く。 後者がこれに該当するかどうかは明確ではないが、該当する場合は前者よりも重要である。 詳しくは、この後の「スタンドアロンの CREATE SCHEMA ステートメント」を参照してほしい。
「第 4 章 カタログとスキーマ」p58 *2
3.3 SET IDENTITY_INSERT
テーブルの ID 列に明示的な値を追加することを許可します。
SET IDENTITY_INSERT (Transact-SQL)
ID 列の自動的な生成を一時的に無効にするには、SET IDENTITY_INSERT tablename ON オプションを使用します。 このオプションは、ID シーケンスのギャップを埋めるだけでなく、以前の値がすでに存在しているデータの一括読み込みなどのタスクに有益です。
「6.4 IDENTITY プロパティ」p251 *1
3.4 sp_executesql
何回も再利用可能な、または動的に作成した Transact-SQL ステートメントやバッチを実行します。 Transact-SQL ステートメントやバッチには、埋め込みパラメーターを含めることができます。
sp_executesql を呼び出すバッチ内で宣言されている変数は、sp_executesql バッチから参照できません。 sp_executesql バッチ内のローカル カーソルまたはローカル変数は、sp_executesql を呼び出すバッチでは認識されません。
sp_executesql (Transact-SQL)
sp_executesql コマンドにはインターフェイスがあるため、EXEC(<string>) よりも柔軟性が高く、入力と出力の両方のパラメーターがサポートされます。 この機能により、EXEC よりも効果的に実行プランを再利用できる引数を使用して、クエリ文字列を作成出来ます。 コードを動的に作成する点を除いて、sp_executesql のコンポーネントはストアドプロシージャのコンポーネントに非常に似ています。 sp_executesql のコンポーネントには、コードのバッチ、パラメータ宣言セクション、パラメータ割り当てセクションがあります。
sp_executesql には、出力パラメータを使用して、呼び出し元のバッチで定義された変数に値を返すインターフェイスのサポート関連の強力な機能もあります。 この機能により、一時テーブルを介してデータを返す必要がなくなるため、コードが効率化し、再コンパイルの回数も減ります。 出力パラメータを定義して使用するための構文は、ストアドプロシージャの構文と同じです。 つまり、OUTPUT 句を指定するパラメータを宣言する必要があり、呼び出hし元のバッチで、定義された変数のポインタをパラメータに割り当てる場合も、OUTPUT 句を指定する必要があります。
「8.2.1 sp_executesql のインターフェイス」pp401-403 *3
A 参照
*1 Delaney, Kalen 『インサイド Microsoft SQL Server 2005 ストレージエンジン編』2007 年 日経 BP ソフトプレス ISBN 9784891005504
*2 Date, C. J. = Darwen, Hugh 『標準 SQL ガイド 改定第 4 版』1999 年 アスキー ISBN 4756120474
