• [アーティクル]

ストアド プロシージャの設計 (データベース エンジン)

バッチとして記述できる Transact-SQL コードはほぼすべて、ストアド プロシージャの作成に使用できます。

ストアド プロシージャの設計上の規則

ストアド プロシージャを設計する場合の規則を次に示します。

  • CREATE PROCEDURE の定義自体には、次のステートメントを除き、あらゆる種類の SQL ステートメントをいくつでも含めることができます。次のステートメントは、ストアド プロシージャ内では使用できません。

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE FUNCTION または ALTER FUNCTION

    CREATE TRIGGER または ALTER TRIGGER

    CREATE PROCEDURE または ALTER PROCEDURE

    CREATE VIEW または ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • ストアド プロシージャ内で、他のデータベース オブジェクトを作成できます。オブジェクトが参照される前に作成されている限り、そのストアド プロシージャ内で作成されたオブジェクトを参照できます。

  • ストアド プロシージャ内で一時テーブルを参照できます。

  • ストアド プロシージャ内でローカルな一時テーブルを作成した場合、この一時テーブルはそのストアド プロシージャのためだけに存在するので、ストアド プロシージャを終了すると使用できなくなります。

  • 別のストアド プロシージャを呼び出すストアド プロシージャを実行する場合、呼び出されたストアド プロシージャは、最初のストアド プロシージャが作成した、一時テーブルを含むすべてのオブジェクトにアクセスできます。

  • Microsoft SQL Server のリモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャはトランザクションにはかかわりません。

  • ストアド プロシージャのパラメータの最大数は 2100 です。

  • ストアド プロシージャのローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。

  • 使用可能なメモリによって異なりますが、ストアド プロシージャの最大サイズは 128 MB です。

ストアド プロシージャ内での名前の修飾

ストアド プロシージャ内では、スキーマにより修飾されていないオブジェクト名がステートメント (SELECT や INSERT など) で使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。ストアド プロシージャ内の SELECT、INSERT、UPDATE、または DELETE のステートメントで参照しているテーブルまたはビューの名前を、ストアド プロシージャを作成したユーザーが修飾していない場合、ストアド プロシージャからそれらのテーブルにアクセスできるのは、既定では、プロシージャの作成者だけになります。

他のユーザーがストアド プロシージャを使用するには、CREATE、ALTER、DROP の各ステートメント、DBCC ステートメント、EXECUTE ステートメント、動的 SQL ステートメントなど、すべての DDL (データ定義言語) ステートメントで使用するオブジェクト名を、オブジェクト スキーマの名前で修飾する必要があります。この場合、オブジェクトにスキーマの名前を指定することで、どのユーザーがストアド プロシージャを呼び出しても、名前が確実に同じオブジェクトに解決されます。SQL Server はスキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします。

プロシージャの定義の難読化

CREATE PROCEDURE ステートメントの元のテキストを難読化した形式に変換するには、WITH ENCRYPTION オプションを使用します。難読化の出力は、SQL Server 2008 のシステム テーブルまたはビューのいずれにも直接表示されません。システム テーブル、システム ビュー、またはデータベース ファイルへのアクセス権のないユーザーは、難読化テキストを取得できません。ただし、直接データベース ファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。このような権限を持っているユーザーは、難読化をリバース エンジニアリングしてストアド プロシージャ定義の元のテキストを取得できます。

SET ステートメントのオプション

Transact-SQL ストアド プロシージャが作成または変更される場合、データベース エンジンでは、SET QUOTED_IDENTIFIER と SET ANSI_NULLS の両方の設定が保存されます。これらの元の設定は、ストアド プロシージャの実行時に使用されます。したがって、ストアド プロシージャの実行中は、SET QUOTED_IDENTIFIER と SET ANSI_NULLS のすべてのクライアント セッションの設定は無視されます。ストアド プロシージャ内の SET QUOTED_IDENTIFIER ステートメントと SET ANSI_NULLS ステートメントは、ストアド プロシージャの機能に影響しません。

SET ARITHABORT、SET ANSI_WARNINGS、SET ANSI_PADDINGS など、他の SET オプションは、ストアド プロシージャの作成時または変更時に保存されません。ストアド プロシージャのロジックが特定の設定に依存する場合は、プロシージャの先頭に SET ステートメントを挿入して、適切な設定を確保します。ストアド プロシージャから SET ステートメントを実行すると、その設定は、そのストアド プロシージャが終了するまでの期間のみ有効になります。ストアド プロシージャが終了すると、その設定は、ストアド プロシージャが呼び出されたときの値に戻ります。この機能を利用すれば、各クライアントはストアド プロシージャのロジックに影響を与えずに必要なオプションを設定することができます。

注意注

ストアド プロシージャやユーザー定義関数にパラメータを渡す場合、またはバッチ ステートメントで変数を宣言して設定する場合に、ANSI_WARNINGS を使用することはお勧めしません。たとえば、変数を char(3) と定義し、これに 4 文字以上の値を設定すると、データが定義されたサイズに合わせて切り捨てられてから、INSERT または UPDATE ステートメントが成功します。