CREATE PROCEDURE (Transact-SQL)

構文

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

引数

• schema_name
  プロシージャが属するスキーマの名前を指定します。プロシージャはスキーマ バインドされています。プロシージャの作成時にスキーマ名が指定されていない場合、プロシージャを作成しているユーザーの既定のスキーマが自動的に割り当てられます。スキーマの詳細については、「ユーザーとスキーマの分離」を参照してください。

• procedure_name
  プロシージャの名前を指定します。プロシージャ名は、識別子のルールに従うと共に、スキーマ内で一意であることが必要です。

  プロシージャ名を付けるときに sp_ プレフィックスを使用しないでください。このプレフィックスは、SQL Server でシステム プロシージャを指定するために使用されるものです。このプレフィックスを使用すると、同じ名前のシステム プロシージャがある場合にアプリケーション コードが機能しなくなる可能性があります。詳細については、「ストアド プロシージャの設計 (データベース エンジン)」を参照してください。

  ローカルまたはグローバルの一時プロシージャを作成するには、procedure_name の前に、ローカル一時プロシージャの場合は番号記号 (#) を 1 つ付加し (#procedure_name)、グローバル一時プロシージャの場合は番号記号を 2 つ付加します (##procedure_name)。ローカル一時プロシージャは、そのプロシージャを作成した接続のみに表示されます。接続が閉じられると、ローカル一時プロシージャは削除されます。グローバル一時プロシージャは、すべての接続で使用できます。プロシージャを使用する最後のセッションが終了すると、グローバル一時プロシージャは削除されます。CLR プロシージャには一時名は指定できません。

  プロシージャまたはグローバル一時プロシージャの名前は、## を含め最大で半角 128 文字です。ローカル一時プロシージャの名前は、# を含め最大で半角 116 文字です。

• **;**number
  同じ名前のプロシージャのグループ化に使用される整数を指定します (省略可能)。グループ化したプロシージャは、DROP PROCEDURE ステートメントの 1 回の実行でまとめて削除できます。

  注
  この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。

  番号付きプロシージャでは xml または CLR ユーザー定義型を使用できません。また、番号付きプロシージャはプラン ガイドで使用することができません。

• @parameter
  プロシージャ内で宣言されているパラメーターです。最初の文字をアット マーク (@) にしてパラメーター名を指定します。パラメーター名は識別子のルールに従っている必要があります。パラメーターはプロシージャに対してローカルです。同じパラメーター名を他のプロシージャで使用できます。

  1 つ以上のパラメーター (最大 2,100 個) を宣言できます。宣言される各パラメーターの値は、パラメーターに既定値が定義されていない場合、または別のパラメーターと同じ値を使用するよう設定されていない場合は、プロシージャの呼び出し時にユーザーが指定する必要があります。プロシージャにテーブル値パラメーターが含まれていて、そのパラメーターが呼び出しに含まれていない場合、空のテーブルが渡されます。パラメーターは定数式の代わりにのみ使用することができます。テーブル名、列名、またはその他のデータベース オブジェクト名の代わりにパラメーターを使用することはできません。詳細については、「EXECUTE (Transact-SQL)」を参照してください。

  FOR REPLICATION を指定した場合、パラメーターは宣言できません。

• [ type_schema_name**.**] data_type
  パラメーターのデータ型とそのデータ型が属するスキーマを指定します。

  Transact-SQL プロシージャ向けのデータ型に関するガイドライン:

  • Transact-SQL のすべてのデータ型をパラメーターとして使用できます。

  • ユーザー定義テーブル型を使用して、テーブル値パラメーターを作成できます。テーブル値パラメーターは入力パラメーターとしてのみ指定でき、READONLY キーワードと共に使用する必要があります。詳細については、「テーブル値パラメーター (データベース エンジン)」を参照してください。

  • cursor データ型は出力パラメーターでのみ指定でき、VARYING キーワードと共に使用する必要があります。

  CLR プロシージャ向けのデータ型に関するガイドライン:

  • マネージ コードに同等の型を持つネイティブの SQL Server データ型であれば、どの型でもパラメーターとして使用できます。CLR 型と SQL Server システム データ型の対応関係の詳細については、「CLR パラメーター データのマッピング」を参照してください。SQL Server システム データ型とその構文の詳細については、「データ型 (Transact-SQL)」を参照してください。

  • テーブル値または cursor データ型をパラメーターとして使用することはできません。

  • パラメーターのデータ型が CLR ユーザー定義型の場合は、その型に対する EXECUTE 権限が必要です。

• VARYING
  出力パラメーターとしてサポートされている結果セットを指定します。このパラメーターはプロシージャによって動的に作成され、その内容は変化します。cursor パラメーターにのみ適用されます。このオプションは、CLR プロシージャでは無効です。

• default
  パラメーターの既定値を指定します。パラメーターの既定値が定義されている場合は、パラメーターに値を指定せずにプロシージャを実行できます。既定値は定数にする必要がありますが、NULL にすることもできます。定数値はワイルドカードの形式にすることができるため、パラメーターをプロシージャに渡すときに LIKE キーワードを使用することが可能です。後半の例 C を参照してください。

  既定値は、CLR プロシージャの場合のみ、sys.parameters.default 列に記録されます。この列は、Transact-SQL プロシージャ パラメーターでは NULL になります。

• OUT | OUTPUT
  パラメーターが出力パラメーターであることを示します。プロシージャの呼び出し元に値を返すには、OUTPUT パラメーターを使用します。text、ntext、および image パラメーターは、プロシージャが CLR プロシージャでない限り、OUTPUT パラメーターとして使用できません。出力パラメーターは、プロシージャが CLR プロシージャでない限り、カーソルのプレースホルダーにできます。テーブル値データ型をプロシージャの OUTPUT パラメーターとして指定することはできません。

• READONLY
  パラメーターをプロシージャの本体内で更新または変更できないことを示します。パラメーターの型がテーブル値型の場合は、READONLY を指定する必要があります。

• RECOMPILE
  データベース エンジンでこのプロシージャ用のクエリ プランをキャッシュせず、プロシージャが実行されるたびにコンパイルされるようにすることを示します。再コンパイルを強制する理由に関する詳細については、「ストアド プロシージャの再コンパイル」を参照してください。このオプションは、FOR REPLICATION を指定した場合または CLR プロシージャには使用できません。

  データベース エンジンでプロシージャ内にある個々のクエリに対するプランを破棄するには、クエリの定義で RECOMPILE クエリ ヒントを使用します。詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。

• ENCRYPTION
  SQL Server で、CREATE PROCEDURE ステートメントの元のテキストを、暗号化した形式に変換することを示します。暗号化した形式の出力は、SQL Server 内のどのカタログ ビューでも直接見ることはできません。システム テーブルまたはデータベース ファイルへのアクセス権を持たないユーザーは、暗号化した形式のテキストを取得できません。ただし、DAC ポート経由でシステム テーブルにアクセスする権限、または直接データベース ファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。また、サーバー プロセスにデバッガーをアタッチできるユーザーは、実行時に、暗号化を解除したプロシージャをメモリから取得できます。システム メタデータへのアクセスの詳細については、「メタデータ表示の構成」を参照してください。

  このオプションは、CLR プロシージャでは無効です。

  このオプションを使って作成したプロシージャを、SQL Server レプリケーションの一部として発行することはできません。

• EXECUTE AS
  プロシージャを実行するセキュリティ コンテキストを指定します。

  詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。

• FOR REPLICATION
  プロシージャがレプリケーション用に作成されていることを指定します。したがって、サブスクライバーで実行することはできません。FOR REPLICATION オプションを指定して作成したプロシージャは、プロシージャ フィルターとして使用され、レプリケーション時にのみ実行されます。FOR REPLICATION を指定した場合、パラメーターは宣言できません。CLR プロシージャには FOR REPLICATION は指定できません。RECOMPILE オプションは、FOR REPLICATION を使って作成されたプロシージャでは無視されます。

  FOR REPLICATION プロシージャでは、sys.objects および sys.procedures でオブジェクトの種類 RF が指定されます。

• { [ BEGIN ] sql_statement [;] [ ...n ][ END ] }
  プロシージャの本体を構成する 1 つ以上の Transact-SQL ステートメントを指定します。省略可能な BEGIN キーワードと END キーワードを使用して、ステートメントを囲むことができます。詳細については、後に続く「ベスト プラクティス」、「全般的な解説」、および「制限事項と制約事項」を参照してください。

• EXTERNAL NAME assembly_name**.class_name.method_name
  CLR プロシージャで参照する .NET Framework アセンブリのメソッドを指定します。class_name は有効な SQL Server 識別子で、アセンブリ内にクラスとして存在する必要があります。クラス名に名前空間とその区切り文字のピリオド (.) が含まれる場合は、クラス名をかっこ ([]) または引用符 (""**) で区切る必要があります。指定するメソッドは、クラスの静的メソッドであることが必要です。

  既定では、SQL Server は CLR コードを実行できません。共通言語ランタイム モジュールを参照するデータベース オブジェクトを作成、変更、および削除することはできますが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、sp_configure を使用します。

ベスト プラクティス

これはベスト プラクティスをすべて網羅した一覧ではありませんが、次の推奨事項に従うと、プロシージャのパフォーマンスが向上する可能性があります。

• プロシージャの本体の最初のステートメントとして SET NOCOUNT ON ステートメントを使用する。つまり、SET NOCOUNT ON ステートメントを AS キーワードの直後に配置します。こうすると、SELECT、INSERT、UPDATE、MERGE、および DELETE ステートメントの実行後に、SQL Server がクライアントに送り返すメッセージが表示されません。この不要なネットワーク オーバーヘッドを軽減することにより、データベースとアプリケーションの全体的なパフォーマンスが向上します。詳細については、「SET NOCOUNT (Transact-SQL)」を参照してください。

• プロシージャ内のデータベース オプションを作成または参照するときにスキーマ名を使用する。これにより、複数のスキーマを検索する必要がない場合に、データベース エンジンでオブジェクト名の解決に要する時間が減少します。また、スキーマを指定せずにオブジェクトを作成したときに、割り当てられているユーザーの既定のスキーマが原因で権限とアクセスの問題が発生するのを防ぐこともできます。詳細については、「ユーザーとスキーマの分離」を参照してください。

• WHERE 句と JOIN 句で指定された列で関数がラップされないようにする。これにより、列が不明確になるため、クエリ プロセッサでインデックスが使用されません。

• 多くのデータ行を返す SELECT ステートメントでは、スカラー関数を使用しない。スカラー関数はすべての行に適用する必要があるため、行ベースの処理と同じような動作になり、パフォーマンスが低下します。

• SELECT * を使用しない。代わりに、必要な列名を指定します。これにより、プロシージャの実行を停止するデータベース エンジンのエラーのいくつかを回避できます。たとえば、12 列のテーブルからデータを返してから、そのデータを 12 列の一時テーブルに挿入する SELECT * ステートメントは、いずれかのテーブルの列数または列の順序が変更されない限り成功します。

• 大量のデータを処理したり返したりしない。プロシージャ コードでできるだけ早く結果を絞り、プロシージャによって実行される後続の操作ができる限り最小のデータ セットを使用して実行されるようにします。基本的なデータのみをクライアント アプリケーションに送信します。その方が、ネットワーク経由で余分なデータを送信して、クライアント アプリケーションで不必要に大きな結果セットを処理させるよりも効率的です。

• BEGIN/END TRANSACTION による明示的なトランザクションを使用し、トランザクションをできるだけ短くする。トランザクションが長いほど、レコードのロックが長くなり、デッドロックが発生する可能性が高くなります。詳細については、「ロックおよび行のバージョン管理」、「ロックの互換性 (データベース エンジン)」、または「データベース エンジンにおける分離レベル」を参照してください。

• プロシージャ内のエラー処理に Transact-SQL TRY…CATCH 機能を使用する。TRY…CATCH は Transact-SQL ステートメントのブロック全体をカプセル化できます。これにより、パフォーマンスのオーバーヘッドが低減されるだけでなく、大幅に少ないプログラミングでエラー レポートをより正確なものにすることができます。詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。

• プロシージャの本体の Transact-SQL ステートメント CREATE TABLE または ALTER TABLE によって参照されているすべてのテーブル列で DEFAULT キーワードを使用する。これにより、NULL 値を許可しない列に NULL が渡されるのを防ぐことができます。

• 一時テーブルの各列に NULL または NOT NULL を使用する。CREATE TABLE および ALTER TABLE ステートメントで NULL または NOT NULL 属性が指定されていない場合、ANSI_DFLT_ON および ANSI_DFLT_OFF オプションを使用すると、データベース エンジンが NULL や NOT NULL 属性を列に割り当てる方法を制御できます。ある接続でこれらのオプションを使用してプロシージャを実行する場合、オプションの設定がプロシージャを作成した接続時と異なっていると、新しい接続で作成されるテーブルの列に対して、異なる NULL 許容属性や異なる動作を指定することができます。各列に NULL または NOT NULL を明示的に宣言すると、プロシージャを実行するすべての接続に対して同じ NULL 許容属性を使用することにより、複数の一時テーブルが作成されます。

• NULL を変換する変更ステートメントを使用し、クエリから NULL 値を含む行を除外するロジックを含める。Transact-SQL では、NULL は空の値ではなく、"Nothing" 値であることに注意してください。NULL は不明な値のためのプレースホルダーなので、特に結果セットのクエリを実行しているときや集計関数を使用している場合に、予期しない動作が発生することがあります。詳細については、「NULL 比較検索条件」および「NULL 値」を参照してください。

• 個別の値に特定のニーズがない限り、UNION または OR 演算子の代わりに UNION ALL 演算子を使用する。フィルターによって重複が結果セットから除外されないため、UNION ALL 演算子の処理オーバーヘッドが少なくなります。

全般的な解説

プロシージャの最大サイズはあらかじめ定義されていません。

プロシージャ内の変数には、ユーザー定義変数や @@SPID などのシステム変数を使用できます。

プロシージャは最初の実行時にコンパイルされ、データを取得するための最適なアクセス プランが決定されます。プロシージャがデータベース エンジンのプラン キャッシュに残っている場合、次にそのストアド プロシージャを実行するときには生成済みのプランを再使用できます。詳細については、「実行プランのキャッシュと再利用」または「ストアド プロシージャとトリガの実行」を参照してください。

SQL Server の起動時に、1 つ以上のストアド プロシージャを自動的に実行できます。このプロシージャは、システム管理者によって master データベースに作成される必要があり、sysadmin 固定サーバー ロールの下でバックグラウンド プロセスとして実行される必要があります。プロシージャに入力または出力パラメーターを指定することはできません。詳細については、「ストアド プロシージャの実行 (データベース エンジン)」を参照してください。

プロシージャは、別のプロシージャを呼び出す場合、または CLR ルーチン、型、集計を参照してマネージ コードを実行する場合に入れ子になります。プロシージャとマネージ コード参照は、32 レベルまで入れ子にすることができます。入れ子のレベルは、呼び出されたプロシージャまたはマネージ コード参照の実行が開始されると 1 つ増加し、呼び出されたプロシージャまたはマネージ コード参照の実行が終了されると 1 つ減少します。マネージ コード内から呼び出されたメソッドは、この入れ子レベルの制限としてはカウントされません。ただし、CLR ストアド プロシージャで、SQL Server マネージ プロバイダーを利用してデータ アクセス操作が実行される場合、マネージ コードから SQL への移行時に入れ子のレベルが 1 つ追加されます。

入れ子の最高レベルを越える呼び出しを行うと、一連の呼び出しが失敗します。@@NESTLEVEL 関数を使用すると、現在実行中のストアド プロシージャの入れ子レベルを返すことができます。

相互運用性

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

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

SET ステートメントは、SET SHOWPLAN_TEXT および SET SHOWPLAN_ALL を除き、プロシージャ内部で指定できます。バッチで同時に他のステートメントを実行することはできません。選択した SET オプションは、プロシージャの実行中は有効で、実行後に元の設定に戻されます。SET オプションの詳細については、「SET オプション」を参照してください。

注
プロシージャでパラメーターを引き渡す場合や、バッチ ステートメントで変数を宣言または設定する場合、またはユーザー定義関数においては、SET ANSI_WARNINGS は無視されます。たとえば、変数を char(3) として定義し、3 文字より長い値を指定すると、データは定義されたサイズに切り捨てられ、INSERT または UPDATE ステートメントは成功します。

制限事項と制約事項

1 つのバッチ内に CREATE PROCEDURE ステートメントと他の Transact-SQL ステートメントを混在させることはできません。

次のステートメントは、ストアド プロシージャの本体内のどこにも使用できません。

プロシージャはまだ存在していないテーブルを参照できます。作成時には、構文チェックのみが行われます。プロシージャは、最初の実行時までコンパイルされません。プロシージャ内で参照されているすべてのオブジェクトが解決されるのは、コンパイル時のみです。したがって、存在しないテーブルを参照するプロシージャは、構文が正しければ正常に作成できます。ただし、実行時に参照されるテーブルが存在しない場合、プロシージャは失敗します。詳細については、「名前の遅延解決とコンパイル」を参照してください。

関数名をパラメーターの既定値またはプロシージャの実行時にパラメーターに渡される値として指定することはできません。ただし、次の例に示すように、関数を変数として渡すことができます。

-- Passing the function value as a variable.
DECLARE @CheckDate datetime = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; 
GO

このプロシージャによって SQL Server のリモート インスタンス上で変更を行うと、それらの変更はロールバックできません。リモート プロシージャはトランザクションにはかかわりません。詳細については、「サーバー間のリモート ストアド プロシージャのエラー処理」を参照してください。

データベース エンジンが .NET Framework でオーバーロードされるときに正しいメソッドを参照するようにするには、EXTERNAL NAME 句で指定されるメソッドに以下の特性が必要です。

• 静的メソッドとして宣言される。

• プロシージャのパラメーター数と同じ数のパラメーターを受け取る。

• SQL Server プロシージャの対応するパラメーターのデータ型と互換性のあるパラメーター型を使用する。SQL Server データ型に一致する .NET Framework データ型については、「CLR パラメーター データのマッピング」を参照してください。

メタデータ

次の表に、ストアド プロシージャに関する情報を返すために使用できるカタログ ビューおよび動的管理ビューを示します。

コンパイル後のプロシージャのサイズを予測するには、Cache Hit Ratio、Cache Pages、および Cache Object Counts というパフォーマンス モニター カウンターを使用します。詳細については、「SQL Server の Plan Cache オブジェクト」を参照してください。

セキュリティ

権限

使用例

基本構文

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.uspMultipleResults 
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

パラメーターの引き渡し

このセクションの例では、入力パラメーターと出力パラメーターを使用してストアド プロシージャとの間で値を受け渡しする方法を示します。

A. 入力パラメーターを使用したプロシージャを作成する

次の例では、特定の 1 人の従業員の姓と名の値を渡すことで、その従業員に関する情報を返すストアド プロシージャを作成します。このプロシージャは、渡されたパラメーターと完全に一致するものだけを受け入れます。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployees プロシージャは次のように実行されます。

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

B. ワイルドカード パラメーターを含むプロシージャを使用する

次の例では、従業員の姓と名を表す値のすべてまたは一部を渡して従業員に関する情報を返すストアド プロシージャを作成します。このプロシージャでは、渡されるパラメーターのパターン マッチが行われます。パラメーターが指定されない場合は、あらかじめ設定された既定値が使用されます (姓の先頭文字が D)。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

uspGetEmployees2 プロシージャは、多くの組み合わせで実行できます。ここでは、一部の組み合わせのみを示します。

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

C. OUTPUT パラメーターを使用する

次の例では、uspGetList プロシージャを作成します。このプロシージャは、指定の価格以下の製品の一覧を返します。この例では、複数の SELECT ステートメントと複数の OUTPUT パラメーターを使用します。OUTPUT パラメーターを使用すると、プロシージャの実行中に、外部プロシージャ、バッチ、または複数の Transact-SQL ステートメントから、値セットにアクセスできます。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

uspGetList を実行し、$700 より安い Adventure Works 製品 (バイク) の一覧を返します。ここではフロー制御と共に OUTPUT パラメーターの @Cost および @ComparePrices を使用して、Messages ウィンドウにメッセージを返します。

注
OUTPUT 変数は、プロシージャの作成時と変数の使用時に定義する必要があります。パラメーター名と変数名は一致する必要はありませんが、データ型とパラメーターの位置は一致する必要があります。ただし、@ListPrice = variable が使用されている場合を除きます。

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

次に結果セットの一部を示します。

Product                     List Price

--------------------------  ----------

Road-750 Black, 58          539.99

Mountain-500 Silver, 40     564.99

Mountain-500 Silver, 42     564.99

...

Road-750 Black, 48          539.99

Road-750 Black, 52          539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

D. テーブル値パラメーターを使用する

次の例では、テーブル値パラメーターの型を使用して、テーブルに複数の行を挿入します。さらに、パラメーターの型を作成して、その型を参照するテーブル変数を宣言し、パラメーター一覧を入力して値をストアド プロシージャに渡します。このストアド プロシージャでは、値を使用して、テーブルに複数の行を挿入します。

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

E. OUTPUT カーソル パラメーターを使用する

次の例では、OUTPUT カーソル パラメーターを使用して、プロシージャに対してローカルになっているカーソルを、呼び出し側のバッチ、プロシージャ、またはトリガーに戻します。

まず、Currency テーブルに対してカーソルを宣言し、そのカーソルをオープンするプロシージャを作成します。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

次に、ローカル カーソル変数を宣言し、ローカル変数にカーソルを割り当てるプロシージャを実行した後、カーソルから行を取り出すバッチを実行します。

USE AdventureWorks2008R2;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

ストアド プロシージャを使用したデータの変更

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

エラー処理

USE AdventureWorks2008R2;
GO

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS
SET NOCOUNT ON;
BEGIN TRY
   BEGIN TRANSACTION 
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;

/* Intentionally generate an error by reversing the order in which rows are deleted from the
   parent and child tables. This change does not cause an error when the procedure
   definition is altered, but produces an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS

BEGIN TRY
   BEGIN TRANSACTION 
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT TRANSACTION

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;

DROP PROCEDURE Production.uspDeleteWorkOrder;

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

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

USE AdventureWorks2008R2;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

プロシージャの強制再コンパイル

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

セキュリティ コンテキストの設定

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

関連項目

参照

概念

その他の技術情報