次の方法で共有

EXECUTE (Transact-SQL)

  • [アーティクル]

Transact-SQL バッチ内のコマンド文字列または文字列を実行するか、システム ストアド プロシージャ、ユーザー定義ストアド プロシージャ、CLR ストアド プロシージャ、ユーザー定義のスカラー値関数、または拡張ストアド プロシージャのうちいずれかのモジュールを実行します。

セキュリティに関する注意セキュリティに関する注意

文字列で EXECUTE を呼び出す前には、文字列を検証してください。 また、検証されていないユーザー入力から作成されるコマンドは実行しないでください。

SQL Server では、EXECUTE ステートメントが拡張され、パススルー コマンドをリンク サーバーに送信できるようになりました。 さらに、文字列またはコマンドを実行するコンテキストを、明示的に設定できるようになりました。

WITH RESULT SETS オプションを使用すると、結果セットのメタデータを定義できます。

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

構文

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH <execute_option> [ ,...n ] ]
    }
[;]

Execute a character string
{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]
[;]

Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]
        [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
    ) 
    [ AS { LOGIN | USER } = ' name ' ]
    [ AT linked_server_name ]
[;]

<execute_option>::=
{
        RECOMPILE 
    | { RESULT SETS UNDEFINED } 
    | { RESULT SETS NONE } 
    | { RESULT SETS ( <result_sets_definition> [,...n ] ) }
} 

<result_sets_definition> ::= 
{
    (
         { column_name 
           data_type 
         [ COLLATE collation_name ] 
         [ NULL | NOT NULL ] }
         [,...n ]
    )
    | AS OBJECT 
        [ db_name . [ schema_name ] . | schema_name . ] 
        {table_name | view_name | table_valued_function_name }
    | AS TYPE [ schema_name.]table_type_name
    | AS FOR XML 
}

引数

  • @return\_status
    モジュールから返されるステータス番号を格納する整数型の変数を指定します (省略可能)。 この変数は、EXECUTE ステートメントで使う前に、バッチ、ストアド プロシージャ、または関数の中で宣言する必要があります。

    @return\_status 変数をユーザー定義のスカラー値関数の呼び出しに使用する場合は、任意のスカラー データ型を指定できます。

  • module_name
    呼び出されるストアド プロシージャやユーザー定義のスカラー値関数の、完全修飾名または部分的な修飾名を指定します。 モジュール名は、識別子の規則に従っている必要があります。 拡張ストアド プロシージャの名前では、サーバーの照合順序に関係なく、常に大文字と小文字が区別されます。

    別のデータベース内で作成されたモジュールを実行するには、実行するユーザーがモジュールを所有しているか、そのデータベース内のモジュールを実行する適切な権限がユーザーに与えられている必要があります。 SQL Server を実行している別のサーバーでモジュールを実行するには、実行するユーザーに対して、そのサーバーを使用する適切な権限 (リモート アクセス) と、そのデータベース内のモジュールを実行する適切な権限が与えられている必要があります。 サーバー名だけを指定してデータベース名を指定しない場合、SQL Server データベース エンジンでは、ユーザーの既定のデータベース内でモジュールが検索されます。

  • ;number
    同じ名前のプロシージャのグループ化に使用される整数です (省略可能)。 このパラメーターは、拡張ストアド プロシージャでは使用できません。

    注意注

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

    プロシージャ グループの詳細については、「CREATE PROCEDURE (Transact-SQL)」を参照してください。

  • @module\_name\_var
    モジュール名を表すローカル変数の名前を指定します。

  • @parameter
    モジュールで定義される、module_name 用のパラメーターを指定します。 パラメーター名の先頭にはアット マーク (@) を付ける必要があります。 @parameter\_name=value の形式で使用する場合、パラメーター名と定数は、モジュールで定義されている順序で指定する必要はありません。 ただし、@parameter_name=value の形式をパラメーターとして使用した場合は、以降のすべてのパラメーターは、この形式で指定する必要があります。

    既定では、パラメーターに NULL 値は許容されます。

  • value
    モジュールまたはパススルー コマンドに渡すパラメーターの値を指定します。 パラメーター名を指定しない場合、パラメーター値は、モジュールで定義された順序で指定する必要があります。

    リンク サーバーに対してパススルー コマンドを実行するとき、パラメーター値の順序は、リンク サーバーの OLE DB プロバイダーに依存します。 ほとんどの OLE DB プロバイダーでは、左から右へパラメーターに値がバインドされます。

    パラメーターの値がオブジェクト名や文字列であったり、データベース名やスキーマ名によって修飾されている場合は、その名前全体を単一引用符で囲む必要があります。 パラメーターの値がキーワードの場合は、そのキーワードを二重引用符で囲む必要があります。

    既定値がモジュール内で定義されている場合、ユーザーはパラメーターを指定せずにモジュールを実行できます。

    既定値を NULL にすることもできます。 パラメーター値が NULL の場合の操作は、通常、モジュールの定義で指定します。

  • @variable
    パラメーターと戻りパラメーターを格納する変数です。

  • OUTPUT
    モジュールまたはコマンド文字列でパラメーターを返すよう指定します。 モジュールまたはコマンド文字列内で一致するパラメーターも、キーワード OUTPUT を使って作成されている必要があります。 このキーワードは、カーソル変数をパラメーターとして使用するときに指定します。

    リンク サーバーに対して実行するモジュールの OUTPUT として value を定義した場合、対応する @parameter に対して、OLE DB プロバイダーによる変更が加えられていると、その変更内容がモジュール実行の最後に変数にコピーされて戻されます。

    OUTPUT パラメーターを使用し、呼び出し元のバッチまたはモジュール内の他のステートメントで戻り値を使用する場合は、パラメーターの値を @parameter = @variable のように変数として渡す必要があります。 モジュール内で OUTPUT パラメーターとして定義されていないパラメーターには、OUTPUT を指定してモジュールを実行することはできません。 OUTPUT を使って定数をモジュールに引き渡すことはできません。戻りパラメーターには変数名が必要です。 プロシージャを実行する前に、必ず変数のデータ型を宣言し、値を割り当ててください。

    リモート ストアド プロシージャに対して EXECUTE を使用する場合や、リンク サーバーに対してパススルー コマンドを実行する場合は、OUTPUT パラメーターにラージ オブジェクト (LOB) 型を指定することはできません。

    戻りパラメーターには、LOB 型以外のデータ型を指定できます。

  • DEFAULT
    モジュールで定義されているパラメーターの既定値を使用します。 パラメーターに値を必要とするモジュールでパラメーターの既定値が定義されていない場合、パラメーターを指定しなかったり、DEFAULT キーワードを指定したりすると、エラーが発生します。

  • @string\_variable
    ローカル変数の名前を指定します。 @string\_variable のデータ型として、char、varchar、nchar、nvarchar のいずれかを指定できます。 これには、(max) 型も含まれます。

  • [N] 'tsql_string'
    定数文字列を指定します。 tsql_string には、nvarchar または varchar 型が許可されます。 N が含まれる場合、文字列は nvarchar 型として解釈されます。

  • AS <context_specification>
    ステートメントを実行するコンテキストを指定します。

  • LOGIN
    権限を借用するコンテキストがログインであることを指定します。 権限借用のスコープはサーバーです。

  • USER
    権限を借用するコンテキストが、現在のデータベース内のユーザーであることを指定します。 権限借用のスコープは、現在のデータベースに限定されます。 コンテキスト スイッチの対象がデータベース ユーザーであっても、そのユーザーのサーバー レベルの権限は継承されません。

    重要な注意事項重要

    データベース ユーザーに対するコンテキスト スイッチがアクティブであるときに、データベース外部のリソースへアクセスを試みると、ステートメントが失敗する原因となります。 たとえば、USE database ステートメントや分散クエリ、3 部または 4 部構成の識別子を使用する別のデータベースを参照するクエリなどは実行しないでください。

  • 'name'
    有効なユーザーまたはログイン名を指定します。 name は、固定サーバー ロール sysadmin のメンバーであるか、sys.database_principals または sys.server_principals のプリンシパルとして存在する必要があります。

    name には、NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService、NT AUTHORITY\LocalSystem などのビルトイン アカウントは指定できません。

    詳細については、後の「ユーザーまたはログイン名の指定」を参照してください。

  • [N] 'command_string'
    リンク サーバーにパススルーされるコマンドを含む定数文字列を指定します。 N が含まれる場合、文字列は nvarchar 型として解釈されます。

  • [?]
    パススルー コマンドの <arg-list> で値が提供されるパラメーターを表します。このパススルー コマンドは、EXEC('...', <arg-list>) AT <linkedsrv> ステートメントで使用されるものです。

  • AT linked_server_name
    command_string を linked_server_name に対して実行し、結果が返された場合はそれをクライアントに返します。 linked_server_name は、ローカル サーバー内の既存のリンク サーバー定義を参照している必要があります。 リンク サーバーは、sp_addlinkedserver を使って定義されます。

  • WITH <execute_option>
    実行オプションは次のとおりです。 RESULT SETS オプションは INSERT…EXEC ステートメントで指定できません。

    用語

    定義

    RECOMPILE

    モジュール実行後に、新しいプランを強制的にコンパイル、使用、および破棄します。 モジュールに既存のクエリ プランがある場合、このプランはキャッシュに残ります。

    指定するパラメーターが一定しない場合であったり、データが大きく変更されたときにこのオプションを使用してください。 このオプションは、拡張ストアド プロシージャには使用しません。 このオプションは負荷を伴うので、あまり使用しないことをお勧めします。

    注意注

    OPENDATASOURCE 構文を使用するストアド プロシージャを呼び出す場合、WITH RECOMPILE は使用できません。 4 部構成のオブジェクト名が指定されている場合、WITH RECOMPILE オプションは無視されます。

    RESULT SETS UNDEFINED

    このオプションを使用した場合、返される結果の種類は保証されず、定義は指定されません。 ステートメントは、何かの結果が返される場合でも、結果が返されない場合でも、問題なく実行されます。 result_sets_option を指定しない場合は、RESULT SETS UNDEFINED が既定の動作となります。

    RESULT SETS NONE

    実行ステートメントによって結果が一切返されなくなります。 結果が返された場合、バッチは中断されます。

    <result_sets_definition>

    result_sets_definition で指定されたとおりに結果が返されるようになります。 複数の結果セットを返すステートメントの場合は、複数の result_sets_definition セクションを指定してください。 その際には、各 result_sets_definition をかっこで囲み、コンマで区切ります。 詳細については、このトピックの「<result_sets_definition>」を参照してください。

  • <result_sets_definition>
    実行されたステートメントによって返される結果セットの定義です。 result_sets_definition の句には、次の意味があります。

    項目

    定義

    {

        column_name

        data_type

        [ COLLATE collation_name]

        [NULL | NOT NULL]

    }

    項目

    定義

    column_name

    各列の名前です。 列の数が結果セットと異なる場合は、エラーが発生し、バッチが中断されます。 列の名前が結果セットと異なる場合は、返される列の名前は定義済みの名前に設定されます。

    data_type

    各列のデータ型です。 データ型が異なる場合は、定義済みのデータ型への暗黙的な変換が行われます。 変換に失敗すると、バッチが中断されます。

    COLLATE collation_name

    各列の照合順序です。 照合順序の不一致がある場合、暗黙的な照合順序が使用されます。 これに失敗した場合は、バッチが中断されます。

    NULL | NOT NULL

    各列の NULL 値の許容属性です。 定義済みの NULL 値の許容属性が NOT NULL である場合に、返されるデータに NULL が含まれていると、エラーが発生し、バッチは中断されます。 指定しない場合、既定値は ANSI_NULL_DFLT_ON および ANSI_NULL_DFLT_OFF オプションの設定に従った値になります。

    db_name

    テーブル、ビュー、またはテーブル値関数を含むデータベースの名前です。

    schema_name

    テーブル、ビュー、またはテーブル値関数を所有するスキーマの名前です。

    table_name | view_name | table_valued_function_name

    名前付きのテーブル、ビュー、またはテーブル値関数で指定された列を返すように指定します。 テーブル変数、一時テーブル、およびシノニムは、AS オブジェクト構文ではサポートされていません。

    AS TYPE [schema_name.]table_type_name

    テーブル型で指定された列を返すように指定します。

    AS FOR XML

    EXECUTE ステートメントで呼び出されたステートメントまたはストアド プロシージャからの XML の結果を、SELECT … FOR XML … ステートメントで生成された場合と同様の形式に変換するように指定します。 元のステートメントの TYPE ディレクティブからのすべての書式設定が削除され、TYPE ディレクティブが指定されなかったものとして、結果が返されます。 AS FOR XML では、実行されたステートメントまたはストアド プロシージャからの XML 以外の表形式の結果は XML に変換されません。

    実行中に返される実際の結果セットは、結果セットの数、列の数、列の名前、NULL 値の許容属性、およびデータ型のいずれかが WITH RESULT SETS 句を使用して定義された結果とは異なる場合があります。 結果セットの数が異なる場合は、エラーが発生し、バッチが中断されます。

説明

パラメーターは、value または @parameter\_name = value を使用して指定できます。パラメーターはトランザクションの一部ではないため、トランザクションでパラメーターが変更され、後でロールバックされても、パラメーターの値を前の値に戻すことはできません。 呼び出し元に返される値は常に、モジュールから戻る時点での値になります。

1 つのモジュールで、別のモジュールが呼び出されるか、共通言語ランタイム (CLR) モジュール、ユーザー定義型、または集計の参照によりマネージ コードが実行されるとき、入れ子が発生します。 入れ子のレベルは、呼び出されたモジュールまたはマネージ コード参照の実行開始時に増加し、呼び出されたモジュールやマネージ コード参照の終了時に減少します。 入れ子のレベルが最大値 32 を超えると、呼び出しチェーン全体が失敗します。 現在の入れ子レベルは、@@NESTLEVEL システム関数に格納されます。

リモート プロシージャと拡張ストアド プロシージャは、BEGIN DISTRIBUTED TRANSACTION ステートメントの中で実行されない限り、または各種の構成オプションと共に使用されない限り、トランザクションのスコープ外となります。したがって、これらのプロシージャを呼び出して実行したコマンドはロールバックできません。 詳細については、「システム ストアド プロシージャ (Transact-SQL)」および「BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)」を参照してください。

カーソル変数を使用するとき、カーソル変数にカーソルを渡すプロシージャを実行し、このカーソルがプロシージャに割り当てられているカーソルであった場合は、エラーが発生します。

ステートメントがバッチ内の最初のステートメントの場合は、モジュールを実行するときに EXECUTE キーワードを指定する必要はありません。

CLR ストアド プロシージャに固有のその他の情報については、「CLR ストアド プロシージャ」を参照してください。

ストアド プロシージャでの EXECUTE の使用

ステートメントがバッチ内の最初のステートメントの場合は、ストアド プロシージャを実行するときに EXECUTE キーワードを指定する必要はありません。

SQL Server システム ストアド プロシージャは、sp_ で始まります。 システム ストアド プロシージャは、物理的にはリソース データベースに格納されますが、論理的にはシステムおよびユーザー定義の各データベースの sys スキーマに属します。 システム ストアド プロシージャを、バッチ内、またはユーザー定義ストアド プロシージャや関数などのモジュール内で実行する場合は、ストアド プロシージャ名に sys スキーマ名を追加することをお勧めします。

SQL Server システム拡張ストアド プロシージャは、xp_, という文字で始まり、master データベースの dbo スキーマに含まれています。 システム拡張ストアド プロシージャを、バッチ内、またはユーザー定義ストアド プロシージャや関数などのモジュール内で実行する場合は、ストアド プロシージャ名に master.dbo を追加することをお勧めします。

ユーザー定義ストアド プロシージャを、バッチ内、またはユーザー定義ストアド プロシージャや関数などのモジュール内で実行する場合は、ストアド プロシージャ名にスキーマ名を追加することをお勧めします。 ユーザー定義ストアド プロシージャに、システム ストアド プロシージャと同じ名前を付けることはお勧めしません。 ストアド プロシージャの実行の詳細については、「ストアド プロシージャの実行」を参照してください。

文字列での EXECUTE の使用

以前のバージョンの SQL Server では、文字列は 8,000 バイトに制限されています。 このため、動的実行では大きな文字列を連結する必要があります。 SQL Server では、varchar(max) 型と nvarchar(max) 型を指定でき、2 GB までの文字列データを使用できるようになりました。

データベース コンテキスト内の変更が維持されるのは、EXECUTE ステートメントの終了時までです。 たとえば、次のステートメントの EXEC を実行すると、データベース コンテキストは master になります。

USE master; EXEC ('USE AdventureWorks2012; SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee;');

コンテキストの切り替え

AS { LOGIN | USER } = ' name ' 句を使用して、動的ステートメントの実行コンテキストを切り替えることができます。 コンテキスト スイッチを EXECUTE ('string') AS <context_specification> のように指定した場合、コンテキスト スイッチは、実行するクエリのスコープでのみ有効になります。

ユーザーまたはログイン名の指定

AS { LOGIN | USER } = ' name ' で指定するユーザーまたはログイン名は、sys.database_principals または sys.server_principals の各プリンシパルとして存在する必要があります。存在しない場合、ステートメントは失敗します。 さらに、プリンシパルで IMPERSONATE 権限が許可されている必要があります。 呼び出し元がデータベース所有者または固定サーバー ロール sysadmin のメンバーでない場合は、ユーザーが Windows グループ メンバーシップによって SQL Server のデータベースやインスタンスにアクセスしているときでも、プリンシパルは存在する必要があります。 たとえば、次のような状況を想定します。

  • CompanyDomain\SQLUsers グループに Sales データベースへのアクセス権がある。

  • CompanyDomain\SqlUser1 は SQLUsers のメンバーであり、したがって Sales データベースへのアクセスが暗黙的に許可されている。

この場合、CompanyDomain\SqlUser1 は SQLUsers グループのメンバーシップを介してデータベースにアクセスできますが、CompanyDomain\SqlUser1 がプリンシパルとしてデータベースに存在していないので、ステートメント EXECUTE @string\_variable AS USER = 'CompanyDomain\SqlUser1' は失敗します。

ベスト プラクティス

ステートメントまたはモジュールで定義されている操作の実行に必要な、最小の権限が与えられているログインまたはユーザーを指定します。 たとえば、データベース レベルの権限だけが必要な場合、サーバー レベルの権限が与えられているログイン名は指定しません。また、データベース所有者アカウントは、データベース レベルの権限が必要とされない場合は指定しません。

権限

EXECUTE ステートメントの実行に権限は必要ありませんが、 EXECUTE 文字列内で参照されるセキュリティ保護可能なリソースに対しては権限が必要です。 たとえば、文字列に INSERT ステートメントが含まれる場合、EXECUTE ステートメントの呼び出し元には、対象のテーブルに対する INSERT 権限が必要です。 EXECUTE ステートメントは、モジュール内に含まれている場合でも、検出されるたびに権限が確認されます。

モジュールの EXECUTE 権限は、既定ではモジュールの所有者に与えられており、所有者はその権限を別のユーザーに譲渡できます。 文字列を実行するモジュールが実行されるとき、権限は、モジュールを作成したユーザーのコンテキストではなく、モジュールを実行しているユーザーのコンテキストに基づいて確認されます。 ただし、呼び出すモジュールと呼び出されるモジュールを所有するユーザーが同じ場合、2 番目のモジュールに対しては EXECUTE 権限の確認は行われません。

モジュールで他のデータベース オブジェクトにアクセスする場合は、モジュールに対する EXECUTE 権限があり、次のいずれかに該当する場合にのみ、実行は成功します。

  • モジュールが EXECUTE AS USER または SELF としてマークされており、モジュール所有者が、参照されるオブジェクトに対して必要な権限を保持している。 モジュール内の権限借用の詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。

  • モジュールが EXECUTE AS CALLER としてマークされており、オブジェクトに対して必要な権限を保持している。

  • モジュールが EXECUTE AS user_name としてマークされており、user_name が、オブジェクトに対して必要な権限を保持している。

コンテキスト切り替え権限

ログインに EXECUTE AS を指定するには、呼び出し元に、指定のログイン名に対する IMPERSONATE 権限が与えられている必要があります。 データベース ユーザーに EXECUTE AS を指定するには、呼び出し元に、指定のユーザー名に対する IMPERSONATE 権限が与えられている必要があります。 実行コンテキストを指定しない場合、または EXECUTE AS CALLER を指定する場合、IMPERSONATE 権限は必要ありません。

使用例

A. EXECUTE を使用して 1 つのパラメーターを渡す

uspGetEmployeeManagers ストアド プロシージャでは、1 つのパラメーター (@EmployeeID) を使用することが前提となっています。 次の例では、Employee ID 6 をパラメーター値として使用し、uspGetEmployeeManagers ストアド プロシージャを実行します。

USE AdventureWorks2012;
GO
EXEC dbo.uspGetEmployeeManagers 6;
GO

実行の中で明示的に変数を指定することもできます。

EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

次のコードがバッチまたは osql または sqlcmd スクリプト内の最初のステートメントの場合、EXEC は必要ありません。

dbo.uspGetEmployeeManagers 6;
GO
--Or
dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

B. 複数のパラメーターを使用する

次の例では、spGetWhereUsedProductID ストアド プロシージャを実行します。 ここでは、製品 ID (819) と、datetime 型の値をとる @CheckDate の、2 つのパラメーターを引き渡します。

USE AdventureWorks2012;
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

C. EXECUTE 'tsql_string' を変数と共に使用する

次の例では、変数を含み、動的に構築される文字列が EXECUTE でどのように処理されるかを示します。 この例では、tables_cursor カーソルを作成します。このカーソルは、 AdventureWorks2012 データベース内にあるすべてのユーザー定義テーブルの一覧を保持しています。次にその一覧を使用して、テーブルに対してすべてのインデックスを再構築します。

USE AdventureWorks2012;
GO
DECLARE tables_cursor CURSOR
   FOR
   SELECT s.name, t.name 
   FROM sys.objects AS t
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id
   WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
   EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
   FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO

D. EXECUTE をリモート ストアド プロシージャと共に使用する

次の例では、リモート サーバー SQLSERVER1 で uspGetEmployeeManagers ストアド プロシージャを実行し、@retstat に成功または失敗を示す戻りステータスを格納します。

DECLARE @retstat int;
EXECUTE @retstat = SQLSERVER1.AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityID = 6;

E. EXECUTE をストアド プロシージャ変数と共に使用する

次の例では、ストアド プロシージャ名を表す変数を作成します。

DECLARE @proc_name varchar(30);
SET @proc_name = 'sys.sp_who';
EXEC @proc_name;

F. EXECUTE を DEFAULT と共に使用する

次の例では、第 1 および第 3 パラメーターに既定値を指定して、ストアド プロシージャを作成します。 プロシージャを実行するとき、値を指定しなかったり、既定値が指定されていた場合は、これらの既定値が第 1 および第 3 パラメーターに挿入されます。 DEFAULT キーワードはさまざまな方法で使用できます。

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL
   DROP PROCEDURE dbo.ProcTestDefaults;
GO
-- Create the stored procedure.
CREATE PROCEDURE dbo.ProcTestDefaults (
@p1 smallint = 42, 
@p2 char(1), 
@p3 varchar(8) = 'CAR')
AS 
   SET NOCOUNT ON;
   SELECT @p1, @p2, @p3
;
GO

Proc_Test_Defaults ストアド プロシージャは、多くの組み合わせで実行できます。

-- Specifying a value only for one parameter (@p2).
EXECUTE dbo.ProcTestDefaults @p2 = 'A';
-- Specifying a value for the first two parameters.
EXECUTE dbo.ProcTestDefaults 68, 'B';
-- Specifying a value for all three parameters.
EXECUTE dbo.ProcTestDefaults 68, 'C', 'House';
-- Using the DEFAULT keyword for the first parameter.
EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D';
-- Specifying the parameters in an order different from the order defined in the procedure.
EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E';
-- Using the DEFAULT keyword for the first and third parameters.
EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT;
EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT;

G. EXECUTE を AT linked_server_name と共に使用する

次の例では、コマンド文字列をリモート サーバーに渡します。 ここでは、SQL Server の別のインスタンスをポイントするリンク サーバー SeattleSales を作成し、そのリンク サーバーに対して DDL ステートメント (CREATE TABLE) を実行します。

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks2012.dbo.SalesTbl 
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;
GO

H. EXECUTE WITH RECOMPILE を使用する

次の例では、Proc_Test_Defaults ストアド プロシージャを実行し、モジュール実行後に新しいクエリ プランを強制的にコンパイル、使用、および破棄します。

EXECUTE dbo.Proc_Test_Defaults @p2 = 'A' WITH RECOMPILE;
GO

I. EXECUTE をユーザー定義関数と共に使用する

次の例では、ユーザー定義のスカラー関数 ufnGetSalesOrderStatusText を実行します。 ここでは、変数 @returnstatus を使用して、関数によって返される値を格納します。 この関数には 1 つの入力パラメーター @Status が必要です。 これは tinyint 型として定義されます。

USE AdventureWorks2012;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO

J. EXECUTE を使用して、リンク サーバー上の Oracle データベースに対してクエリを実行する

次の例では、いくつかの SELECT ステートメントを、リモートの Oracle サーバーで実行します。 この例では、まず Oracle サーバーをリンク サーバーとして追加し、リンク サーバー ログインを作成します。

-- Setup the linked server.
EXEC sp_addlinkedserver  
        @server='ORACLE',
        @srvproduct='Oracle',
        @provider='OraOLEDB.Oracle', 
        @datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='ORACLE',
    @useself='false', 
    @locallogin=null, 
    @rmtuser='scott', 
    @rmtpassword='tiger';
 
EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO
 
-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

K. EXECUTE AS USER を使用して、コンテキストを別のユーザーに切り替える

次の例では、テーブルを作成する Transact-SQL 文字列を実行し、AS USER 句を指定して、ステートメントの実行コンテキストを呼び出し元から User1 に切り替えます。 データベース エンジン では、ステートメントの実行時に User1 の権限がチェックされます。 User1 はデータベース内のユーザーとして存在し、Sales スキーマにテーブルを作成する権限が与えられている必要があります。そうでない場合、ステートメントは失敗します。

USE AdventureWorks2012;
GO
EXECUTE ('CREATE TABLE Sales.SalesTable (SalesID int, SalesName varchar(10));')
AS USER = 'User1';
GO

L. EXECUTE および AT linked_server_name と共にパラメーターを使用する

次の例では、パラメーターのプレースホルダーとして疑問符 (?) を使用し、コマンド文字列をリモート サーバーに渡します。 ここでは、SQL Server の別のインスタンスをポイントするリンク サーバー SeattleSales を作成し、そのリンク サーバーに対して SELECT ステートメントを実行します。 SELECT ステートメントでは、ProductID パラメーター (952) のプレースホルダーとして疑問符を使用します。このパラメーターは、ステートメントの後で提供されます。

-- Setup the linked server.
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
-- Execute the SELECT statement.
EXECUTE ('SELECT ProductID, Name 
    FROM AdventureWorks2012.Production.Product
    WHERE ProductID = ? ', 952) AT SeattleSales;
GO

M. EXECUTE を使用して単一の結果セットを再定義する

前のいくつかの例では、7 つの列を返す EXEC dbo.uspGetEmployeeManagers 6; を実行しました。 次の例では、WITH RESULT SET 構文を使用して、返される結果セットの名前とデータ型を変更する方法を示します。

USE AdventureWorks2012;
GO

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(50) NOT NULL,
    [Employee Last Name] nvarchar(50) NOT NULL,
    [Employee ID of Manager] nvarchar(max) NOT NULL,
    [Manager First Name] nvarchar(50) NOT NULL,
    [Manager Last Name] nvarchar(50) NOT NULL )
);

N. EXECUTE を使用して 2 つの結果セットを再定義する

複数の結果セットを返すステートメントを実行する場合は、予期される各結果セットを定義してください。 AdventureWorks2012 の次の例では、2 つの結果セットを返すプロシージャを作成します。 作成したプロシージャはその後 WITH RESULT SETS 句を使用して実行され、2 つの結果セットの定義が指定されます。

USE AdventureWorks2012;
GO

--Create the procedure
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set 
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID 
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure 
EXEC Production.ProductList '%tire%'
WITH RESULT SETS 
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);

関連項目

参照

@@NESTLEVEL (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

EXECUTE AS 句 (Transact-SQL)

osql ユーティリティ

REVERT (Transact-SQL)

sp_addlinkedserver (Transact-SQL)

sqlcmd ユーティリティ

SUSER_NAME (Transact-SQL)

sys.database_principals (Transact-SQL)

sys.server_principals (Transact-SQL)

USER_NAME (Transact-SQL)

OPENDATASOURCE (Transact-SQL)

概念

プリンシパル (データベース エンジン)