CREATE PROCEDURE (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2006 年 12 月 12 日

ストアドプロシージャを作成します。ストアドプロシージャは、Transact-SQL ステートメントをまとめて保存したものです。また Microsoft .NET Framework 共通言語ランタイム (CLR) メソッドへの参照として使用することもできます。このメソッドでは、ユーザー指定のパラメータを受け取り、結果を返すことができます。プロシージャは、永続的な使用、1 つのセッション内での一時使用 (ローカル一時プロシージャ)、または全セッションでの一時使用 (グローバル一時プロシージャ) を目的として作成できます。

ストアドプロシージャは、SQL Server インスタンスの起動時に自動的に実行するよう作成することもできます。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
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 回の実行でまとめて削除できます。たとえば、orders というアプリケーションでは、orderproc;1、orderproc;2 のような名前のプロシージャを使用でき、DROP PROCEDURE orderproc ステートメントを使用すると、グループ全体が削除されます。名前に区切り記号付きの識別子が含まれる場合、数字を識別子に含めることはできません。procedure_name だけを適切な区切り記号で囲む必要があります。

番号付きストアドプロシージャには次の制限があります。

データ型に xml または CLR ユーザー定義型は使用できない。
番号付きストアドプロシージャにはプランガイドを作成できない。

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

**@**parameter
プロシージャ内のパラメータを指定します。CREATE PROCEDURE ステートメント内では 1 つ以上のパラメータを宣言できます。宣言されている各パラメータの値は、パラメータに既定値が定義されているか、別のパラメータと同じ値を使用するよう設定されていない限り、プロシージャの呼び出し時にユーザーが指定する必要があります。ストアドプロシージャには最高 2,100 個のパラメータを指定できます。

パラメータ名は、最初の文字をアットマーク (@) にして指定します。パラメータ名は識別子の規則に従っている必要があります。パラメータはプロシージャに対してローカルです。同じパラメータ名を他のプロシージャで使用できます。既定では、パラメータは定数式の代わりにのみ使用することができます。テーブル名、列名、またはその他のデータベースオブジェクト名の代わりにパラメータを使用することはできません。詳細については、「EXECUTE (Transact-SQL)」を参照してください。

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

[ type_schema_name**.** ] data_type
パラメータのデータ型とそれが属するスキーマを指定します。table 型を除くすべてのデータ型を、Transact-SQL ストアドプロシージャのパラメータとして使用できます。ただし、cursor 型は OUTPUT パラメータにのみ使用できます。cursor 型を指定する場合は、VARYING キーワードと OUTPUT キーワードも指定する必要があります。cursor 型では複数の出力パラメータを指定できます。

CLR ストアドプロシージャの場合、char、varchar、text、ntext、image、cursor、および table 型はパラメータとして指定できません。CLR 型と SQL Server システムデータ型の対応関係の詳細については、「SQL Server データ型と .NET Framework データ型の対応」を参照してください。SQL Server システムデータ型とその構文に関する詳細については、「データ型 (Transact-SQL)」を参照してください。

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

type_schema_name を指定しない場合、SQL Server 2005 データベースエンジンは次の順序で type_name を参照します。
- SQL Server システムデータ型
- 現在のデータベースにおける現在のユーザーに既定のスキーマ
- 現在のデータベースの dbo スキーマ
番号付きストアドプロシージャの場合、データ型は xml または CLR ユーザー定義型にできません。

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

default
パラメータの既定値です。default 値が定義されている場合は、パラメータに値を指定せずにプロシージャを実行できます。既定値は定数にする必要がありますが、NULL にすることもできます。プロシージャで LIKE キーワードと共にパラメータが使用される場合、ワイルドカード文字 %、_、[]、および [^] を含めることができます。

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

OUTPUT
パラメータが出力パラメータであることを示します。このオプションの値は、EXECUTE ステートメントの呼び出しに対して返すことができます。プロシージャの呼び出し元に値を返すには、OUTPUT パラメータを使用します。text、ntext、および image パラメータは、プロシージャが CLR プロシージャでない限り、OUTPUT パラメータとして使用できません。OUTPUT キーワードを使用する出力パラメータは、プロシージャが CLR プロシージャでない限り、カーソルのプレースホルダにできます。

RECOMPILE
データベースエンジンでこのプロシージャ用のプランをキャッシュせず、プロシージャを実行時にコンパイルすることを示します。このオプションは、FOR REPLICATION を指定した場合は使用できません。CLR ストアドプロシージャには RECOMPILE は指定できません。

データベースエンジンでストアドプロシージャ内にある個々のクエリに対するプランを破棄するには、RECOMPILE クエリヒントを使用します。詳細については、「クエリヒント (Transact-SQL)」を参照してください。RECOMPILE クエリヒントを使用するのは、非定型の値や一時的な値が、ストアドプロシージャに属するクエリのサブセットでのみ使用されている場合です。

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

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

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

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

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

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

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

<sql_statement>
プロシージャに含める 1 つ以上の Transact-SQL ステートメントを指定します。適用されるいくつかの制限については、「解説」を参照してください。

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

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

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

解説

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

ユーザー定義ストアドプロシージャは、現在のデータベースでのみ作成できます。一時プロシージャは例外で、これは常に tempdb で作成されます。スキーマ名を指定しない場合は、プロシージャを作成しているユーザーの既定のスキーマが使用されます。スキーマの詳細については、「ユーザーとスキーマの分離」を参照してください。

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

既定では、パラメータに NULL 値は許容されます。NULL パラメータ値が渡され、そのパラメータが、参照列に対して NULL 値を許容しない CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで使用されると、データベースエンジンではエラーが生成されます。NULL 値が許容されない列に NULL パラメータ値が渡されないようにするには、プロシージャにプログラミングロジックを追加するか、CREATE TABLE または ALTER TABLE で DEFAULT キーワードを使用して、列の既定値を指定します。

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

SET オプションの使用

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 ステートメントを実行すると、その設定は、ストアドプロシージャが実行を終了するまでの間だけ有効です。ストアドプロシージャが終了すると、その設定は、ストアドプロシージャが呼び出されたときの値に復元されます。この機能を使用すると、個々のクライアントでストアドプロシージャのロジックに影響を与えずに必要なオプションを設定できます。

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

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

CLR ストアドプロシージャでのパラメータの使用

CLR ストアドプロシージャのパラメータは、スカラ SQL Server システムデータ型の 1 つをとることができます。

データベースエンジンが .NET Framework でオーバーロードされるときに正しいメソッドを参照するようにするには、<method_specifier> で指定されるメソッドが次の条件に該当する必要があります。

静的メソッドとして宣言される。
プロシージャのパラメータ数と同じ数のパラメータを受け取る。
自身のクラスのコンストラクタまたはデストラクタではない。
SQL Server プロシージャの対応するパラメータのデータ型と互換性のあるパラメータ型を使用する。SQL Server データ型に一致する .NET Framework データ型については、「SQL Server データ型と .NET Framework データ型の対応」を参照してください。
void または SQLInt32、SQLInt16、System.Int32、または System.Int16 型の値のいずれかを返す。
特定のパラメータ宣言に OUTPUT を指定する場合、パラメータを値ではなく参照で返す。

ストアドプロシージャに関する情報の取得

Transact-SQL ストアドプロシージャの定義を表示するには、そのプロシージャが存在するデータベースの sys.sql_modules カタログビューを使用します。

次に例を示します。

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

メモ :
ENCRYPTION オプションで作成されるストアドプロシージャのテキストは、sys.sql_modules カタログビューを使って表示できません。

プロシージャが参照するオブジェクトについてのレポートを取得するには、sys.sql_dependencies カタログビューにクエリを実行するか、sp_depends を使用します。sp_depends では、CLR ストアドプロシージャが参照するオブジェクトについての情報は返されません。CLR ストアドプロシージャに関する情報を表示するには、プロシージャが存在するデータベースの sys.assembly_modules カタログビューを使用します。

ストアドプロシージャで定義されているパラメータに関する情報を表示するには、プロシージャが存在するデータベースの sys.parameters カタログビューを使用します。

名前の遅延解決

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

ストアドプロシージャの実行

ユーザー定義ストアドプロシージャを実行するとき、バッチ内、またはユーザー定義ストアドプロシージャや関数などのモジュール内で実行する場合は、ストアドプロシージャ名にスキーマ名を追加することを強くお勧めします。

パラメータ値を指定できるようにストアドプロシージャが作成されている場合は、パラメータ値を指定できます。指定する値は定数または変数であることが必要です。関数名をパラメータ値として指定することはできません。変数の場合、ユーザー定義変数または @@SPID などのシステム変数を指定できます。

詳細については、「ストアドプロシージャの実行 (データベースエンジン)」を参照してください。

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

cursor 型を使用するパラメータ

Transact-SQL ストアドプロシージャでは、OUTPUT パラメータにのみ cursor 型を使用できます。パラメータに cursor 型を指定する場合は、VARYING パラメータと OUTPUT パラメータの両方が必要です。パラメータに VARYING キーワードを指定する場合は、データ型を cursor にして、OUTPUT キーワードを指定する必要があります。詳細については、「OUTPUT パラメータでの cursor データ型の使用」を参照してください。

一時ストアドプロシージャ

データベースエンジンでは、ローカルとグローバルの 2 種類の一時プロシージャがサポートされます。ローカル一時プロシージャは、そのプロシージャが作成された接続でのみ使用できます。グローバル一時プロシージャは、すべての接続で使用することができます。ローカル一時プロシージャは、現在のセッションの終了時に自動的に削除されます。グローバル一時プロシージャは、プロシージャを使用する最後のセッションが終了するときに削除されます。詳細については、「ストアドプロシージャの作成 (データベースエンジン)」を参照してください。

ストアドプロシージャの自動実行

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

ストアドプロシージャの入れ子

ストアドプロシージャは入れ子にできます。つまり、1 つのストアドプロシージャから、別のストアドプロシージャを呼び出すことができます。呼び出されたプロシージャが実行を開始すると入れ子レベルが 1 つ上がり、呼び出されたプロシージャが実行を終了するとレベルが 1 つ下がります。ストアドプロシージャは 32 レベルまで入れ子にできます。詳細については、「ストアドプロシージャの入れ子」を参照してください。

コンパイル後のストアドプロシージャのサイズを予測するには、次のパフォーマンスモニタカウンタを使用します。

パフォーマンスモニタオブジェクト名	パフォーマンスモニタカウンタ名
SQLServer: Plan Cache オブジェクト	Cache Hit Ratio
	Cache Pages
	キャッシュオブジェクト数*

* アドホック SQL、準備された SQL、プロシージャ、トリガなど、キャッシュオブジェクトの種類別にオブジェクトの数を調べることができます。

詳細については、「SQL Server の Plan Cache オブジェクト」を参照してください。

<sql_statement> の制限

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

ストアドプロシージャの所有者以外のユーザーがストアドプロシージャを使用する場合、ストアドプロシージャの内部では、CREATE、ALTER、または DROP ステートメントなどのすべてのデータ定義言語 (DDL) ステートメント、DBCC ステートメント、EXECUTE および動的 SQL ステートメントで使用するオブジェクト名には、オブジェクトスキーマの名前を追加する必要があります。詳細については、「ストアドプロシージャの設計 (データベースエンジン)」を参照してください。

権限

データベースの CREATE PROCEDURE 権限と、プロシージャを作成するスキーマに対する ALTER 権限が必要です。

CLR ストアドプロシージャの場合は、<method_specifier> で参照されるアセンブリの所有権、またはそのアセンブリの REFERENCES 権限が必要です。

例

A. 単純なプロシージャを使用する

次のストアドプロシージャは、全従業員 (氏名を提供) と、その役職および部署名を、ビューから返します。このストアドプロシージャではパラメータをまったく使用しません。

USE AdventureWorks;
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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

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

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

B. 単純なプロシージャをパラメータと共に使用する

次のストアドプロシージャは、特定の 1 人の従業員 (氏名を提供) と、その役職および部署名を、ビューから返します。このストアドプロシージャは、渡されたパラメータと完全に一致するものを受け入れます。

USE AdventureWorks;
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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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';

C. 単純なプロシージャをワイルドカードパラメータと共に使用する

次のストアドプロシージャは、特定の複数の従業員 (氏名を提供) と、その役職および部署名を、ビューから返します。このストアドプロシージャでは、渡されるパラメータのパターンマッチが行われます。パラメータが指定されない場合は、あらかじめ設定された既定値が使用されます (姓の先頭文字が D)。

USE AdventureWorks;
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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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%';

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

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

USE AdventureWorks;
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.

E. WITH RECOMPILE オプションを使用する

WITH RECOMPILE 句は、プロシージャに指定するパラメータが定型的でない場合や、新しい実行プランをメモリにキャッシュまたは保存したくない場合に役立ちます。

USE AdventureWorks;
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.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

F. WITH ENCRYPTION オプションを使用する

次の例では、HumanResources.uspEncryptThis ストアドプロシージャを作成します。

USE AdventureWorks;
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 EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

次の例に示すように、WITH ENCRYPTION オプションを使用すると、ストアドプロシージャの定義が返されません。

sp_helptext を実行します。

EXEC sp_helptext 'HumanResources.uspEncryptThis';

以下に結果セットを示します。

The text for object 'HumanResources.uspEncryptThis' is encrypted.

sys.sql_modules カタログビューに直接クエリを実行します。

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

以下に結果セットを示します。

definition
----------------------
NULL

(1 row(s) affected)

G. 名前の遅延解決を使用する

次の例では、uspProc1 プロシージャを作成します。ここでは名前の遅延解決を使用します。ストアドプロシージャは、参照されるテーブルがコンパイル時に存在しなくても作成されます。ただし、プロシージャの実行時にはテーブルが存在する必要があります。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

ストアドプロシージャが作成されたことを確認するには、次のクエリを実行します。

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

以下に結果セットを示します。

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

H. EXECUTE AS 句を使用する

次の例では、EXECUTE AS 句を使用して、ストアドプロシージャを実行できるセキュリティコンテキストを指定します。この例では、オプションの CALLER により、プロシージャを呼び出したユーザーのコンテキストでプロシージャを実行できることを指定します。

USE AdventureWorks;
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 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

I. CLR ストアドプロシージャを作成する

次の例では、GetPhotoFromDB ストアドプロシージャを作成します。このストアドプロシージャでは、HandlingLOBUsingCLR アセンブリ内の LargeObjectBinary クラスの GetPhotoFromDB メソッドを参照します。ストアドプロシージャを作成する前に、HandlingLOBUsingCLR アセンブリはローカルデータベースに登録されます。

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

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

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

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

USE AdventureWorks;
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 AdventureWorks;
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

参照

その他の技術情報

バッチ
 カーソル (データベースエンジン)
ストアドプロシージャ (データベースエンジン)
変数とパラメータの使用 (データベースエンジン)
ストアドプロシージャを作成する方法 (SQL Server Management Studio)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 12 月 12 日	変更内容 : 「解説」に、ストアドプロシージャの最大サイズがあらかじめ定義されていないことを明記しました。

次の方法で共有

CREATE PROCEDURE (Transact-SQL)

構文

引数

解説

SET オプションの使用

CLR ストアドプロシージャでのパラメータの使用

ストアドプロシージャに関する情報の取得

名前の遅延解決

ストアドプロシージャの実行

cursor 型を使用するパラメータ

一時ストアドプロシージャ

ストアドプロシージャの自動実行

ストアドプロシージャの入れ子

<sql_statement> の制限

権限

例

A. 単純なプロシージャを使用する

B. 単純なプロシージャをパラメータと共に使用する

C. 単純なプロシージャをワイルドカードパラメータと共に使用する

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

E. WITH RECOMPILE オプションを使用する

F. WITH ENCRYPTION オプションを使用する

G. 名前の遅延解決を使用する

H. EXECUTE AS 句を使用する

I. CLR ストアドプロシージャを作成する

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

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

次の方法で共有

CREATE PROCEDURE (Transact-SQL)

構文

引数

解説

SET オプションの使用

CLR ストアド プロシージャでのパラメータの使用

ストアド プロシージャに関する情報の取得

名前の遅延解決

ストアド プロシージャの実行

cursor 型を使用するパラメータ

一時ストアド プロシージャ

ストアド プロシージャの自動実行

ストアド プロシージャの入れ子

<sql_statement> の制限

権限

例

A. 単純なプロシージャを使用する

B. 単純なプロシージャをパラメータと共に使用する

C. 単純なプロシージャをワイルドカード パラメータと共に使用する

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

E. WITH RECOMPILE オプションを使用する

F. WITH ENCRYPTION オプションを使用する

G. 名前の遅延解決を使用する

H. EXECUTE AS 句を使用する

I. CLR ストアド プロシージャを作成する

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

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

CLR ストアドプロシージャでのパラメータの使用

ストアドプロシージャに関する情報の取得

ストアドプロシージャの実行

一時ストアドプロシージャ

ストアドプロシージャの自動実行

ストアドプロシージャの入れ子

C. 単純なプロシージャをワイルドカードパラメータと共に使用する

I. CLR ストアドプロシージャを作成する

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