CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

適用対象: ○SQL Server (2008 以降) ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL ServerAzure SQL データベースAzure SQL Database、Azure SQL Data Warehouse、および Parallel Data Warehouse で、Transact-SQLTransact-SQL または共通言語ランタイム (CLR) のストアド プロシージャを作成します。Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Azure SQL データベースAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. ストアド プロシージャは、次のことを実行できる点で、他のプログラミング言語のプロシージャに似ています。Stored procedures are similar to procedures in other programming languages in that they can:

  • 入力パラメーターを受け取り、呼び出し元のプロシージャまたはバッチに出力パラメーターの形式で複数の値を返す。Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • 他のプロシージャの呼び出しなど、データベース内での操作を実行するプログラミング ステートメントを含む。Contain programming statements that perform operations in the database, including calling other procedures.

  • 呼び出し元のプロシージャまたはバッチにステータス値を返し、成功、失敗、および失敗の原因を示す。Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

このステートメントを使用すると、現在のデータベースに永続的なプロシージャを作成したり、tempdb データベースに一時プロシージャを作成したりできます。Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

注意

このトピックでは、SQL Server への .NET Framework CLR の統合について説明します。The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR 統合は、Azure SQL DatabaseSQL Database には適用されません。CLR integration does not apply to Azure SQL DatabaseSQL Database.

構文の詳細を読まずに、基本的なストアド プロシージャの例を見るには、「簡単な例」に進んでください。Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] { 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 ]  
-- Transact-SQL Syntax for CLR Stored Procedures  
  
CREATE [ OR ALTER ] { 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 }  
[;]  
-- Transact-SQL Syntax for Natively Compiled Stored Procedures  
  
CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] 
        [ OUT | OUTPUT ] [READONLY] 
    ] [ ,... n ]  
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]  
AS  
{  
  BEGIN ATOMIC WITH (set_option [ ,... n ] )  
sql_statement [;] [ ... n ]  
 [ END ]  
}  
 [;]  
  
<set_option> ::=  
    LANGUAGE =  [ N ] 'language'  
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }  
  | [ DATEFIRST = number ]  
  | [ DATEFORMAT = format ]  
  | [ DELAYED_DURABILITY = { OFF | ON } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  
  
-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

引数Arguments

OR ALTEROR ALTER
適用対象:Azure SQL DatabaseSQL DatabaseSQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 以降)。Applies to: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

既に存在するプロシージャを変更します。Alters the procedure if it already exists.

schema_nameschema_name
プロシージャが属するスキーマの名前を指定します。The name of the schema to which the procedure belongs. プロシージャはスキーマ バインドされています。Procedures are schema-bound. プロシージャの作成時にスキーマ名を指定しない場合は、プロシージャを作成しているユーザーの既定のスキーマが自動的に割り当てられます。If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
プロシージャの名前を指定します。The name of the procedure. プロシージャ名は、識別子のルールに従うと共に、スキーマ内で一意であることが必要です。Procedure names must comply with the rules for identifiers and must be unique within the schema.

プロシージャに名前を付けるときは、sp_ プレフィックスを使用しないでください。Avoid the use of the sp_ prefix when naming procedures. このプレフィックスは、SQL ServerSQL Server でシステム プロシージャを指定するために使用されるものです。This prefix is used by SQL ServerSQL Server to designate system procedures. このプレフィックスを使用すると、同じ名前のシステム プロシージャがある場合にアプリケーション コードが機能しなくなる可能性があります。Using the prefix can cause application code to break if there is a system procedure with the same name.

ローカルまたはグローバルの一時プロシージャを作成するには、procedure_name の前に、ローカル一時プロシージャの場合は番号記号 (#) を 1 つ付加し (#procedure_name)、グローバル一時プロシージャの場合は番号記号を 2 つ付加します (##procedure_name)。Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). ローカル一時プロシージャは、そのプロシージャが作成された接続のみで表示でき、その接続が閉じられると削除されます。A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. グローバル一時プロシージャは、すべての接続で使用でき、そのプロシージャを使用する最後のセッションが終了すると削除されます。A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. CLR プロシージャには一時名は指定できません。Temporary names cannot be specified for CLR procedures.

プロシージャまたはグローバル一時プロシージャの名前は、## を含め最大で半角 128 文字です。The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. ローカル一時プロシージャの名前は、# を含め最大で半角 116 文字です。The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; number; number
適用対象: SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

同じ名前のプロシージャのグループ化に使用される整数を指定します (省略可能)。An optional integer that is used to group procedures of the same name. グループ化したプロシージャは、DROP PROCEDURE ステートメントの 1 回の実行でまとめて削除できます。These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

注意

この機能はメンテナンス モードであり、Microsoft SQL Server の将来のバージョンで削除される可能性があります。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

番号付きプロシージャでは xml または CLR ユーザー定義型を使用できません。また、番号付きプロシージャはプラン ガイドでは使用できません。Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@ parameter@ parameter
プロシージャ内で宣言されているパラメーターを指定します。A parameter declared in the procedure. 最初の文字をアット マーク (@) にしてパラメーター名を指定します。Specify a parameter name by using the at sign (@) as the first character. パラメーター名は識別子のルールに従っている必要があります。The parameter name must comply with the rules for identifiers. パラメーターはプロシージャに対してローカルです。同じパラメーター名を他のプロシージャで使用できます。Parameters are local to the procedure; the same parameter names can be used in other procedures.

1 つ以上のパラメーター (最大 2,100 個) を宣言できます。One or more parameters can be declared; the maximum is 2,100. 宣言される各パラメーターの値は、パラメーターに既定値が定義されていない場合、または別のパラメーターと同じ値を使用するよう設定されていない場合は、プロシージャの呼び出し時にユーザーが指定する必要があります。The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. プロシージャにテーブル値パラメーターが含まれていて、そのパラメーターが呼び出しに含まれてない場合、空のテーブルが渡されます。If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. パラメーターは定数式の代わりにのみ使用することができます。テーブル名、列名、またはその他のデータベース オブジェクト名の代わりにパラメーターを使用することはできません。Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. 詳細については、「 EXECUTE (Transact-SQL)」を参照してください。For more information, see EXECUTE (Transact-SQL).

FOR REPLICATION を指定した場合、パラメーターは宣言できません。Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name.[ type_schema_name. ] data_type] data_type
パラメーターのデータ型とそのデータ型が属するスキーマを指定します。The data type of the parameter and the schema to which the data type belongs.

Transact-SQLTransact-SQL プロシージャに関するガイドライン:Guidelines for Transact-SQLTransact-SQL procedures:

  • Transact-SQLTransact-SQL のすべてのデータ型をパラメーターとして使用できます。All Transact-SQLTransact-SQL data types can be used as parameters.

  • ユーザー定義テーブル型を使用して、テーブル値パラメーターを作成できます。You can use the user-defined table type to create table-valued parameters. テーブル値パラメーターは、入力パラメーターとしてのみ指定でき、READONLY キーワードと共に使用する必要があります。Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. 詳細については、「テーブル値パラメーターの使用 (Database Engine)」を参照してくださいFor more information, see Use Table-Valued Parameters (Database Engine)

  • cursor データ型は、出力パラメーターとしてのみ指定でき、VARYING キーワードと共に使用する必要があります。cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

CLR プロシージャに関するガイドライン:Guidelines for CLR procedures:

  • マネージド コード内に同等の型を持つネイティブの SQL ServerSQL Server データ型であれば、どの型でもパラメーターとして使用できます。All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. CLR 型と SQL ServerSQL Server システム データ型の対応関係の詳細については、「CLR パラメーター データのマッピング」をご覧ください。For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. SQL ServerSQL Server システム データ型とその構文の詳細については、「データ型 (Transact-SQL)」をご覧ください。For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • テーブル値または cursor データ型をパラメーターとして使用することはできません。Table-valued or cursor data types cannot be used as parameters.

  • パラメーターのデータ型が CLR ユーザー定義型の場合は、その型に対する EXECUTE 権限が必要です。If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
出力パラメーターとしてサポートされている結果セットを指定します。Specifies the result set supported as an output parameter. このパラメーターはプロシージャによって動的に作成され、その内容は変化します。This parameter is dynamically constructed by the procedure and its contents may vary. cursor パラメーターにのみ適用されます。Applies only to cursor parameters. このオプションは、CLR プロシージャでは無効です。This option is not valid for CLR procedures.

defaultdefault
パラメーターの既定値です。A default value for a parameter. パラメーターの既定値が定義されている場合は、パラメーターに値を指定せずにプロシージャを実行できます。If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. 既定値は定数にする必要がありますが、NULL にすることもできます。The default value must be a constant or it can be NULL. 定数値はワイルドカードの形式で指定できるため、パラメーターをプロシージャに渡すときに LIKE キーワードを使用することができます。The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

既定値は、CLR プロシージャの場合のみ、sys.parameters.default 列に記録されます。Default values are recorded in the sys.parameters.default column only for CLR procedures. この列は、Transact-SQLTransact-SQL プロシージャ パラメーターでは NULL になります。That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
パラメーターが出力パラメーターであることを示します。Indicates that the parameter is an output parameter. プロシージャの呼び出し元に値を返すには、OUTPUT パラメーターを使用します。Use OUTPUT parameters to return values to the caller of the procedure. textntext、および image パラメーターは、プロシージャが CLR プロシージャでない限り、OUTPUT パラメーターとして使用できません。text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. 出力パラメーターは、プロシージャが CLR プロシージャでない場合は、カーソルのプレースホルダーにできます。An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. テーブル値データ型をプロシージャの OUTPUT パラメーターとして指定することはできません。A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
パラメーターをプロシージャの本体内で更新または変更できないことを示します。Indicates that the parameter cannot be updated or modified within the body of the procedure. パラメーターの型がテーブル値型の場合は、READONLY を指定する必要があります。If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
データベース エンジンDatabase Engineでこのプロシージャ用のクエリ プランをキャッシュせず、プロシージャが実行されるたびにコンパイルされるようにすることを示します。Indicates that the データベース エンジンDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. 再コンパイルを強制する理由の詳細については、「ストアド プロシージャの再コンパイル」をご覧ください。For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. このオプションは、FOR REPLICATION を指定した場合または CLR プロシージャには使用できません。This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

データベース エンジンDatabase Engineでプロシージャ内にある個々のクエリに対するクエリ プランを破棄するには、クエリの定義で RECOMPILE クエリ ヒントを使用します。To instruct the データベース エンジンDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. 詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
適用対象:SQL Server (SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017)、Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server ( SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL データベースAzure SQL Database.

SQL ServerSQL Server で、CREATE PROCEDURE ステートメントの元のテキストを、暗号化した形式に変換することを示します。Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. 暗号化した形式の出力は、SQL ServerSQL Server 内のどのカタログ ビューでも直接見ることはできません。The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. システム テーブルまたはデータベース ファイルへのアクセス権を持たないユーザーは、暗号化した形式のテキストを取得できません。Users who have no access to system tables or database files cannot retrieve the obfuscated text. ただし、DAC ポート経由でシステム テーブルにアクセスする権限、または直接データベース ファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. また、サーバー プロセスにデバッガーをアタッチできるユーザーは、実行時に、暗号化を解除したプロシージャをメモリから取得できます。Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. システム メタデータのアクセス方法について詳しくは、「メタデータ表示の構成」をご覧ください。For more information about accessing system metadata, see Metadata Visibility Configuration.

このオプションは、CLR プロシージャでは無効です。This option is not valid for CLR procedures.

このオプションを使って作成したプロシージャを、SQL ServerSQL Server レプリケーションの一部として発行することはできません。Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS clauseEXECUTE AS clause
プロシージャを実行するセキュリティ コンテキストを指定します。Specifies the security context under which to execute the procedure.

ネイティブ コンパイル ストアド プロシージャの場合、SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降および Azure SQL データベースAzure SQL Database では、EXECUTE AS 句に対して制限はありません。For natively compiled stored procedures, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL データベースAzure SQL Database, there are no limitations on the EXECUTE AS clause. SQL Server 2014 (12.x)SQL Server 2014 (12.x) では、SELF、OWNER、および 'user_name' 句は、ネイティブ コンパイル ストアド プロシージャでサポートされます。In SQL Server 2014 (12.x)SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.

詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
適用対象:SQL Server (SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017)、Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server ( SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL データベースAzure SQL Database.

プロシージャがレプリケーション用に作成されていることを指定します。Specifies that the procedure is created for replication. そのため、サブスクライバーでプロシージャを実行することはできません。Consequently, it cannot be executed on the Subscriber. FOR REPLICATION オプションを指定して作成したプロシージャは、プロシージャ フィルターとして使用され、レプリケーション時にのみ実行されます。A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. FOR REPLICATION を指定した場合、パラメーターは宣言できません。Parameters cannot be declared if FOR REPLICATION is specified. CLR プロシージャには FOR REPLICATION は指定できません。FOR REPLICATION cannot be specified for CLR procedures. RECOMPILE オプションは、FOR REPLICATION を使って作成されたプロシージャでは無視されます。The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

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

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
プロシージャの本体を構成する 1 つ以上の Transact-SQLTransact-SQL ステートメントを指定します。One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. 省略可能な BEGIN キーワードと END キーワードを使用して、ステートメントを囲むことができます。You can use the optional BEGIN and END keywords to enclose the statements. 詳細については、後で説明する「ベスト プラクティス」、「全般的な解説」、および「制限事項と制約事項」をご覧ください。For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

EXTERNAL NAME assembly_name.class_name.method_nameEXTERNAL NAME assembly_name.class_name.method_name
適用対象: SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017SQL データベースSQL DatabaseApplies to: SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL データベースSQL Database.

CLR プロシージャで参照する .NET Framework.NET Framework アセンブリのメソッドを指定します。Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. class_name は、有効な SQL ServerSQL Server 識別子であること、およびアセンブリにクラスとして存在していることが必要です。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 名前空間部分を区切るためにピリオド (.) を使う名前空間修飾名がクラスにある場合は、クラス名をかっこ ([ ]) または引用符 ("") で区切る必要があります。If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). 指定するメソッドは、クラスの静的メソッドであることが必要です。The specified method must be a static method of the class.

既定では、SQL ServerSQL Server は CLR コードを実行できません。By default, SQL ServerSQL Server cannot execute CLR code. 共通言語ランタイム モジュールを参照するデータベース オブジェクトを作成、変更、および削除することはできますが、SQL ServerSQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. このオプションを有効にするには、sp_configure を使用します。To enable the option, use sp_configure.

注意

CLR プロシージャは、包含データベースではサポートされていません。CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

アトミック ストアド プロシージャの実行を示します。Indicates atomic stored procedure execution. 変更はコミットされるか、例外のスローによってすべての変更がロールバックされます。Changes are either committed or all of the changes rolled back by throwing an exception. ネイティブ コンパイル ストアド プロシージャには、ATOMIC WITH ブロックが必要です。The ATOMIC WITH block is required for natively compiled stored procedures.

明示的に RETURN ステートメントによって、または暗黙的に実行の完了によってプロシージャの制御が戻ると (RETURN)、プロシージャによって実行された作業がコミットされます。If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. プロシージャによってスロー (THROW) が行われると、プロシージャによって実行された作業がロールバックされます。If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT はアトミック ブロック内で既定で ON であり、変更できません。XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT は、SQL ServerSQL Server ステートメントによって実行時エラーが発生した場合に、Transact-SQLTransact-SQL が自動的に現在のトランザクションをロールバックするかどうかを指定します。XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

以下の SET オプションは、ATOMIC ブロック内で常に ON であり、変更できません。The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER、ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

SET オプションは、ATOMIC ブロック内部では変更できません。SET options cannot be changed inside ATOMIC blocks. ユーザー セッション内の SET オプションは、ネイティブ コンパイル ストアド プロシージャのスコープでは使用されません。The SET options in the user session are not used in the scope of natively compiled stored procedures. これらのオプションは、コンパイル時に固定されます。These options are fixed at compile time.

BEGIN、ROLLBACK、および COMMIT 操作は、アトミック ブロック内では使用できません。BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

ネイティブ コンパイル ストアド プロシージャごとに、プロシージャのスコープの外部に 1 つの ATOMIC ブロックがあります。There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. ブロックを入れ子にすることはできません。The blocks cannot be nested. ATOMIC ブロックについて詳しくは、「ネイティブ コンパイル ストアド プロシージャ」をご覧ください。For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
パラメーターで NULL 値を許すかどうかを示します。Determines whether null values are allowed in a parameter. NULL が既定値です。NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

プロシージャがネイティブにコンパイルされることを示します。Indicates that the procedure is natively compiled. NATIVE_COMPILATION、SCHEMABINDING、および EXECUTE AS は、任意の順序で指定できます。NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. 詳細については、次を参照してください。 ネイティブ コンパイル ストアド プロシージャです。For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

プロシージャによって参照されるテーブルを削除または変更できないようにします。Ensures that tables that are referenced by a procedure cannot be dropped or altered. ネイティブ コンパイル ストアド プロシージャでは、SCHEMABINDING が必要です。SCHEMABINDING is required in natively compiled stored procedures. 詳しくは、「ネイティブ コンパイル ストアド プロシージャ」をご覧ください。SCHEMABINDING の制限は、ユーザー定義関数に対する場合と同じです。(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. 詳しくは、「CREATE FUNCTION (Transact-SQL)」の SCHEMABINDING のセクションをご覧ください。For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'LANGUAGE = [N] 'language'
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

SET LANGUAGE (Transact-SQL) セッション オプションと同等です。Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'language' は必須です。LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

ネイティブ コンパイル ストアド プロシージャでは必要です。Required for natively compiled stored procedures. ストアド プロシージャのトランザクション分離レベルを指定します。Specifies the transaction isolation level for the stored procedure. 次のオプションがあります。The options are as follows:

これらのオプションについて詳しくは、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」をご覧ください。For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
他のトランザクションによって変更されていてもまだコミットされていないデータは、ステートメントで読み取ることができないことを指定します。Specifies that statements cannot read data that has been modified but not yet committed by other transactions. 現在のトランザクションが読み取ったデータを他のトランザクションが変更した場合、現在のトランザクションは失敗します。If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
次のことを指定します。Specifies the following:

  • 他のトランザクションで変更されたが、まだコミットされていないデータは、ステートメントで読み取れない。Statements cannot read data that has been modified but not yet committed by other transactions.
  • 現在のトランザクションが読み取ったデータを他のトランザクションが変更した場合、現在のトランザクションは失敗します。If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • 現在のトランザクションのいずれかのステートメントが読み取ったキー範囲に該当するキー値を持つ新しい行を、他のトランザクションが挿入した場合、現在のトランザクションは失敗します。If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
トランザクションの各ステートメントで、トランザクション全体で一貫性のあるデータを読み取るように指定します。このデータは、トランザクション開始時点に存在したデータです。Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numberDATEFIRST = number
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

週の最初の曜日を 1 - 7 の数値で指定します。Specifies the first day of the week to a number from 1 through 7. DATEFIRST は省略可能です。DATEFIRST is optional. 指定しない場合、設定は指定された言語から推定されます。If it is not specified, the setting is inferred from the specified language.

詳しくは、「SET DATEFIRST (Transact-SQL)」をご覧ください。For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatDATEFORMAT = format
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

date、smalldatetime、datetime、datetime2、datetimeoffset の各文字列を解釈する際の日付要素 (月、日、年) の順序を指定します。Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT は省略可能です。DATEFORMAT is optional. 指定しない場合、設定は指定された言語から推定されます。If it is not specified, the setting is inferred from the specified language.

詳しくは、「SET DATEFORMAT (Transact-SQL)」をご覧ください。For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
適用対象: SQL Server 2014 (12.x)SQL Server 2014 (12.x) から SQL Server 2017SQL Server 2017 および Azure SQL データベースAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL データベースAzure SQL Database.

SQL ServerSQL Server によるトランザクションのコミットには、完全持続性、既定値、または遅延持続性が適用されます。transaction commits can be either fully durable, the default, or delayed durable.

詳しくは、「トランザクションの持続性の制御」をご覧ください。For more information, see Control Transaction Durability.

簡単な例Simple Examples

作業を始めるときに役立つ 2 つの簡単な例を次に示します。To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB; は、現在のデータベースの名前を返します。SELECT DB_NAME() AS ThisDB; returns the name of the current database.
そのステートメントを次のようにストアド プロシージャにラップすることができます。You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

EXEC What_DB_is_this; というステートメントを使ってストアド プロシージャを呼び出します。Call the store procedure with statement: EXEC What_DB_is_this;

もう少し複雑な例として、プロシージャの柔軟性を高めるために入力パラメーターを提供します。Slightly more complex, is to provide an input parameter to make the procedure more flexible. 例 :For example:

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

プロシージャを呼び出すときに、データベースの ID 番号を提供します。Provide a database id number when you call the procedure. たとえば、EXEC What_DB_is_that 2; では tempdb が返されます。For example, EXEC What_DB_is_that 2; returns tempdb.

他の例については、この記事の最後にある「」をご覧ください。See Examples towards the end of this topic for many more examples.

ベスト プラクティスBest Practices

ここではベスト プラクティスをすべて網羅しているわけではありませんが、次の推奨事項に従うと、プロシージャのパフォーマンスが向上する場合があります。Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • プロシージャの本体の最初のステートメントとして SET NOCOUNT ON ステートメントを使用する。Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. つまり、このステートメントを AS キーワードの直後に配置します。That is, place it just after the AS keyword. これにより、SELECT、INSERT、UPDATE、MERGE、および DELETE ステートメントの実行後に、SQL ServerSQL Server がクライアントに返すメッセージが表示されません。This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. この不要なネットワーク オーバーヘッドを軽減することにより、データベースとアプリケーションの全体的なパフォーマンスが向上します。Overall performance of the database and application is improved by eliminating this unnecessary network overhead. 詳しくは、「SET NOCOUNT (Transact-SQL)」をご覧ください。For information, see SET NOCOUNT (Transact-SQL).

  • プロシージャ内のデータベース オブジェクトを作成または参照するときにスキーマ名を使用する。Use schema names when creating or referencing database objects in the procedure. これにより、複数のスキーマを検索する必要がない場合に、データベース エンジンDatabase Engineでオブジェクト名の解決に要する時間が短縮されます。It takes less processing time for the データベース エンジンDatabase Engine to resolve object names if it does not have to search multiple schemas. また、スキーマを指定せずにオブジェクトを作成したときに割り当てられるユーザーの既定のスキーマが原因で発生する権限とアクセスの問題を防ぐこともできます。It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema.

  • WHERE 句と JOIN 句で指定された列を関数でラップしないようにする。Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. ラップすると、列が非決定的になるため、クエリ プロセッサでインデックスを使用できなくなります。Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • 多くのデータ行を返す SELECT ステートメントではスカラー関数を使用しない。Avoid using scalar functions in SELECT statements that return many rows of data. スカラー関数はすべての行に適用する必要があるため、結果として、行ベースの処理のような動作になり、パフォーマンスが低下します。Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • SELECT * は使わない。Avoid the use of SELECT *. 代わりに、必要な列名を指定します。Instead, specify the required column names. これにより、プロシージャの実行を停止するデータベース エンジンDatabase Engineのエラーのいくつかを回避できます。This can prevent some データベース エンジンDatabase Engine errors that stop procedure execution. たとえば、12 列のテーブルからデータを返し、そのデータを 12 列の一時テーブルに挿入する SELECT * ステートメントは、いずれかのテーブルの列数または列の順序が変更されない限り成功します。For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • 非常に多くのデータを処理したり返したりしない。Avoid processing or returning too much data. プロシージャ コードによってできるだけ早く結果を絞り込むことで、プロシージャが実行する後続の操作で使用されるデータ セットをできる限り小さくします。Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. 基本的なデータのみをクライアント アプリケーションに送信します。Send just the essential data to the client application. その方が、ネットワーク経由で余分なデータを送信して、クライアント アプリケーションで不必要に大きな結果セットを処理させるよりも効率的です。It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • BEGIN/COMMIT TRANSACTION による明示的なトランザクションを使用し、トランザクションをできるだけ短くする。Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. トランザクションが長いほど、レコードのロックが長くなり、デッドロックが発生する可能性が高くなります。Longer transactions mean longer record locking and a greater potential for deadlocking.

  • プロシージャ内のエラー処理に Transact-SQLTransact-SQL TRY...CATCH 機能を使用する。Use the Transact-SQLTransact-SQL TRY...CATCH feature for error handling inside a procedure. TRY...CATCH を使用すると、Transact-SQLTransact-SQL ステートメントのブロック全体をカプセル化できます。TRY...CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. これにより、パフォーマンスのオーバーヘッドが低減されるだけでなく、記述するプログラムの数が大幅に削減され、エラー レポートの精度も向上します。This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • プロシージャの本体の Transact-SQLTransact-SQL ステートメント CREATE TABLE または ALTER TABLE によって参照されているすべてのテーブル列で DEFAULT キーワードを使用する。Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. これにより、NULL 値を許可しない列に NULL が渡されるのを防ぐことができます。This prevents passing NULL to columns that do not allow null values.

  • 一時テーブルの各列に NULL または NOT NULL を使用する。Use NULL or NOT NULL for each column in a temporary table. CREATE TABLE および ALTER TABLE ステートメントで NULL または NOT NULL 属性が指定されていない場合、ANSI_DFLT_ON および ANSI_DFLT_OFF オプションを使用すると、データベース エンジンDatabase Engineが NULL や NOT NULL 属性を列に割り当てる方法を制御できます。The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the データベース エンジンDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. ある接続でこれらのオプションを使用してプロシージャを実行する場合、オプションの設定がプロシージャを作成した接続時と異なっていると、新しい接続で作成されるテーブルの列に対して、異なる NULL 許容属性や異なる動作を指定することができます。If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. 各列に NULL または NOT NULL を明示的に宣言すると、プロシージャを実行するすべての接続に対して同じ NULL 許容属性を使用することにより、複数の一時テーブルが作成されます。If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • NULL を変換する変更ステートメントを使用し、クエリから NULL 値を含む行を除外するロジックを含める。Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Transact-SQLTransact-SQL では、NULL は空の値ではなく、"何もない" 値であることに注意してください。Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. NULL は不明な値を表すプレースホルダーのため、特に結果セットに対してクエリを実行している場合や集計関数を使用している場合は、予期しない動作が発生することがあります。It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • 個別の値に対する特定のニーズがない限り、UNION 演算子または OR 演算子の代わりに UNION ALL 演算子を使用する。Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. フィルターによって重複が結果セットから除外されないため、UNION ALL 演算子の処理オーバーヘッドが少なくなります。The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

全般的な解説General Remarks

プロシージャの最大サイズは、事前には定義されていません。There is no predefined maximum size of a procedure.

プロシージャで指定する変数には、ユーザー定義変数や @@SPID などのシステム変数を使用できます。Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

プロシージャは最初の実行時にコンパイルされ、データを取得するための最適なアクセス プランが決定されます。When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. プロシージャがデータベース エンジンDatabase Engineのプラン キャッシュに残っている場合、次にそのストアド プロシージャを実行するときには生成済みのプランを再使用できます。Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the データベース エンジンDatabase Engine.

SQL ServerSQL Server の起動時に、1 つ以上のプロシージャを自動的に実行できます。One or more procedures can execute automatically when SQL ServerSQL Server starts. このプロシージャは、システム管理者によって master データベースに作成される必要があり、sysadmin 固定サーバー ロールの下でバックグラウンド プロセスとして実行される必要があります。The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. プロシージャに入力または出力パラメーターを指定することはできません。The procedures cannot have any input or output parameters. 詳しくは、「ストアド プロシージャの実行」をご覧ください。For more information, see Execute a Stored Procedure.

プロシージャは、別のプロシージャを呼び出す場合、または CLR ルーチン、型、集計を参照してマネージド コードを実行する場合に入れ子になります。Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. プロシージャとマネージド コード参照は、32 レベルまで入れ子にすることができます。Procedures and managed code references can be nested up to 32 levels. 入れ子のレベルは、呼び出されたプロシージャまたはマネージド コード参照の実行が開始されると 1 つ増加し、呼び出されたプロシージャまたはマネージド コード参照の実行が終了されると 1 つ減少します。The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. マネージド コード内から呼び出されたメソッドは、この入れ子レベルの制限としてはカウントされません。Methods invoked from within the managed code do not count against the nesting level limit. ただし、CLR ストアド プロシージャで、SQL Server マネージド プロバイダーを利用してデータ アクセス操作が実行される場合、マネージド コードから SQL への移行時に入れ子のレベルが 1 つ追加されます。However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

入れ子の最高レベルを超える呼び出しを行うと、一連の呼び出しが失敗します。Attempting to exceed the maximum nesting level causes the entire calling chain to fail. @@NESTLEVEL 関数を使用すると、現在実行中のストアド プロシージャの入れ子レベルを返すことができます。You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

相互運用性Interoperability

データベース エンジンDatabase Engine プロシージャを作成または変更すると、Transact-SQLTransact-SQLでは SET QUOTED_IDENTIFIER と SET ANSI_NULLS の両方の設定が保存されます。The データベース エンジンDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. これらの元の設定は、プロシージャの実行時に使用されます。These original settings are used when the procedure is executed. したがって、プロシージャの実行中は、SET QUOTED_IDENTIFIER と SET ANSI_NULLS のクライアント セッションの設定は無視されます。Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

SET ARITHABORT、SET ANSI_WARNINGS、SET ANSI_PADDINGS など他の SET オプションは、プロシージャの作成時または変更時に保存されません。Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. プロシージャのロジックが特定の設定に依存する場合は、プロシージャの先頭に SET ステートメントを挿入し、適切な設定を確保します。If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. プロシージャから SET ステートメントを実行すると、その設定は、プロシージャが実行を終了するまでの間だけ有効になります。When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. プロシージャが終了すると、その設定は、プロシージャが呼び出されたときの値に復元されます。The setting is then restored to the value the procedure had when it was called. この機能を使用すると、個々のクライアントでプロシージャのロジックに影響を与えずに必要なオプションを設定できます。This enables individual clients to set the options they want without affecting the logic of the procedure.

SET ステートメントは、SET SHOWPLAN_TEXT および SET SHOWPLAN_ALL を除き、プロシージャ内部で指定できます。Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. バッチで同時に他のステートメントを実行することはできません。These must be the only statements in the batch. 選択した SET オプションは、プロシージャの実行中は有効で、実行後に元の設定に戻されます。The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

注意

プロシージャまたはユーザー定義関数でパラメーターを渡す場合や、バッチ ステートメントで変数を宣言または設定する場合には、SET ANSI_WARNINGS は無視されます。SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. たとえば、変数を char(3) と定義し、これに 4 文字以上の値を設定すると、データが定義されたサイズに合わせて切り捨てられてから、INSERT または UPDATE ステートメントが成功します。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

制限事項と制約事項Limitations and Restrictions

1 つのバッチ内に CREATE PROCEDURE ステートメントと他の Transact-SQLTransact-SQL ステートメントを混在させることはできません。The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

次のステートメントは、ストアド プロシージャの本体では使用できません。The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA [SET SHOWPLAN_TEXT]SET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE TRIGGER または ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE FUNCTION または ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE VIEW または ALTER VIEWCREATE or ALTER VIEW USE database_nameUSE database_name
CREATE PROCEDURE または ALTER PROCEDURECREATE or ALTER PROCEDURE [SET PARSEONLY]SET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

プロシージャはまだ存在していないテーブルを参照できます。A procedure can reference tables that do not yet exist. 作成時には、構文チェックのみが行われます。At creation time, only syntax checking is performed. プロシージャは、最初の実行時までコンパイルされません。The procedure is not compiled until it is executed for the first time. プロシージャ内で参照されているすべてのオブジェクトが解決されるのは、コンパイル時のみです。Only during compilation are all objects referenced in the procedure resolved. したがって、存在しないテーブルを参照するプロシージャは、構文が正しければ正常に作成できます。ただし、実行時に参照されるテーブルが存在しない場合、プロシージャは失敗します。Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

関数名を、パラメーターの既定値として指定したり、プロシージャの実行時にパラメーターに渡される値として指定したりできません。You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. ただし、次の例に示すように、関数を変数として渡すことができます。However, you can pass a function as a variable as shown in the following example.

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

このプロシージャによって SQL ServerSQL Server のリモート インスタンス上で変更を行うと、それらの変更はロールバックできません。If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. リモート プロシージャはトランザクションにはかかわりません。Remote procedures do not take part in transactions.

データベース エンジンDatabase Engineが .NET Framework でオーバーロードされるときに正しいメソッドを参照するようにするには、EXTERNAL NAME 句で指定されるメソッドに以下の特性が必要です。For the データベース エンジンDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • 静的メソッドとして宣言される。Be declared as a static method.

  • プロシージャのパラメーター数と同じ数のパラメーターを受け取る。Receive the same number of parameters as the number of parameters of the procedure.

  • SQL ServerSQL Server プロシージャの対応するパラメーターのデータ型と互換性のあるパラメーター型を使用する。Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. .NET Framework.NET Framework データ型に一致する SQL ServerSQL Server データ型については、「CLR パラメーター データのマッピング」をご覧ください。For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

メタデータMetadata

次の表に、ストアド プロシージャに関する情報を返すために使用できるカタログ ビューおよび動的管理ビューを示します。The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

表示View [説明]Description
sys.sql_modulessys.sql_modules Transact-SQLTransact-SQL プロシージャの定義を返します。Returns the definition of a Transact-SQLTransact-SQL procedure. ENCRYPTION オプションで作成されるプロシージャのテキストは、sys.sql_modules カタログ ビューを使って表示できません。The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules CLR プロシージャに関する情報を返します。Returns information about a CLR procedure.
sys.parameterssys.parameters プロシージャで定義されているパラメーターに関する情報を返します。Returns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities プロシージャによって参照されるオブジェクトを返します。Returns the objects that are referenced by a procedure.

コンパイル後のプロシージャのサイズを予測するには、次のパフォーマンス モニター カウンターを使用します。To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

パフォーマンス モニター オブジェクト名Performance Monitor object name パフォーマンス モニター カウンター名Performance Monitor Counter name
SQLServer:Plan Cache オブジェクトSQLServer: Plan Cache Object Cache Hit RatioCache Hit Ratio
Cache PagesCache Pages
Cache Object Counts*Cache Object Counts*

* アドホック Transact-SQLTransact-SQL、準備された Transact-SQLTransact-SQL、プロシージャ、トリガーなど、キャッシュ オブジェクトの種類別にオブジェクトの数を調べることができます。*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. 詳しくは、「SQL Server の Plan Cache オブジェクト」をご覧ください。For more information, see SQL Server, Plan Cache Object.

SecuritySecurity

アクセス許可Permissions

データベースの CREATE PROCEDURE 権限およびプロシージャの作成先となるスキーマの ALTER 権限、または、db_ddladmin 固定データベース ロールのメンバーシップが必要です。Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

CLR ストアド プロシージャの場合は、EXTERNAL NAME 句で参照されるアセンブリの所有権、またはそのアセンブリの REFERENCES 権限が必要です。For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE とメモリ最適化テーブルCREATE PROCEDURE and Memory-Optimized Tables

メモリ最適化テーブルには、従来のストアド プロシージャとネイティブ コンパイル ストアド プロシージャの両方からアクセスできます。Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. ほとんどの場合、ネイティブ プロシージャの方が効率的です。Native procedures are in most cases the more efficient way. 詳しくは、「ネイティブ コンパイル ストアド プロシージャ」をご覧ください。For more information, see Natively Compiled Stored Procedures.

次のサンプルでは、メモリ最適化テーブル dbo.Departments にアクセスするネイティブ コンパイル ストアド プロシージャを作成する方法を示します。The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  
  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO  

NATIVE_COMPILATION なしで作成されたプロシージャをネイティブ コンパイル ストアド プロシージャに変更することはできません。A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

ネイティブ コンパイル ストアド プロシージャでのプログラミング機能、サポートされるクエリ対象領域、および演算子について詳しくは、「ネイティブ コンパイル T-SQL モジュールでサポートされる機能」をご覧ください。For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

使用例Examples

カテゴリCategory 主な構文要素Featured syntax elements
基本構文Basic Syntax CREATE PROCEDURECREATE PROCEDURE
パラメーターの引き渡しPassing parameters @parameter
   • = default   • = default
   • OUTPUT   • OUTPUT
   • テーブル値パラメーター型   • table-valued parameter type
   • CURSOR VARYING   • CURSOR VARYING
ストアド プロシージャを使用したデータの変更Modifying data by using a stored procedure UPDATEUPDATE
エラー処理Error Handling TRY...CATCHTRY...CATCH
プロシージャの定義の難読化Obfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
プロシージャの強制再コンパイルForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
セキュリティ コンテキストの設定Setting the Security Context EXECUTE ASEXECUTE AS

基本構文Basic Syntax

このセクションの例では、最低限必要な構文を使用して CREATE PROCEDURE ステートメントの基本機能を示します。Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. 単純な Transact-SQL プロシージャを作成するCreating a simple Transact-SQL procedure

次の例は、全従業員 (フルネーム) とその役職および部署名を、AdventureWorks2012AdventureWorks2012 データベースのビューから返すストアド プロシージャを作成します。The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. このプロシージャではパラメーターを使用しません。This procedure does not use any parameters. その後、3 つのメソッドを使用してプロシージャを実行します。The example then demonstrates three methods of executing the procedure.

CREATE PROCEDURE HumanResources.uspGetAllEmployees  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment;  
GO  
  
SELECT * FROM HumanResources.vEmployeeDepartment;  

uspGetEmployees プロシージャは次のように実行されます。The uspGetEmployees procedure can be executed in the following ways:

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

B.B. 複数の結果セットを返すReturning more than one result set

次のプロシージャでは、2 つの結果セットが返されます。The following procedure returns two result sets.

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

C.C. CLR ストアド プロシージャを作成するCreating a CLR stored procedure

次の例では、GetPhotoFromDB プロシージャを作成します。このプロシージャでは、HandlingLOBUsingCLR アセンブリ内の LargeObjectBinary クラスの GetPhotoFromDB メソッドを参照します。The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. プロシージャが作成される前に、HandlingLOBUsingCLR アセンブリがローカル データベースに登録されます。Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

適用対象: SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017SQL データベースSQL Database (assembly_bits から作成されたアセンブリを使用する場合)。Applies to: SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL データベースSQL Database (if using an assembly created from assembly_bits.

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  

パラメーターの引き渡しPassing Parameters

このセクションの例では、入力パラメーターと出力パラメーターを使用してストアド プロシージャ間で値を受け渡す方法を示します。Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. 入力パラメーターを使用したプロシージャを作成するCreating a procedure with input parameters

次の例では、特定の 1 人の従業員の姓と名の値を渡すことで、その従業員に関する情報を返すストアド プロシージャを作成します。The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. このプロシージャは、渡されたパラメーターと完全に一致するものだけを受け入れます。This procedure accepts only exact matches for the parameters passed.

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 プロシージャは次のように実行されます。The uspGetEmployees procedure can be executed in the following ways:

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';  
  

E.E. ワイルドカード パラメーターを含むプロシージャを使用するUsing a procedure with wildcard parameters

次の例では、従業員の姓と名を表す値のすべてまたは一部を渡して従業員に関する情報を返すストアド プロシージャを作成します。The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. このプロシージャでは、渡されるパラメーターのパターン マッチが行われます。パラメーターが指定されない場合は、あらかじめ設定された既定値が使用されます (姓の先頭文字が D)。This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).

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;  

uspGetEmployees2 プロシージャは、多くの組み合わせで実行できます。The uspGetEmployees2 procedure can be executed in many combinations. ここでは、一部の組み合わせのみを示します。Only a few possible combinations are shown here.

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%';  

F.F. OUTPUT パラメーターを使用するUsing OUTPUT parameters

次の例では、uspGetList プロシージャを作成します。The following example creates the uspGetList procedure. このプロシージャは、指定の価格以下の製品の一覧を返します。This procedures returns a list of products that have prices that do not exceed a specified amount. この例では、複数の SELECT ステートメントと複数の OUTPUT パラメーターを使用します。The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT パラメーターを使用すると、プロシージャの実行中に、外部プロシージャ、バッチ、または複数の Transact-SQLTransact-SQL ステートメントから、値セットにアクセスできます。OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 WorksAdventure Works 製品 (バイク) の一覧を返します。Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. ここではフロー制御言語と共に OUTPUT パラメーターの @Cost および @ComparePrices を使用して、[メッセージ] ウィンドウにメッセージを返します。The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

注意

OUTPUT 変数は、プロシージャの作成時と変数の使用時に定義する必要があります。The OUTPUT variable must be defined when the procedure is created and also when the variable is used. パラメーター名と変数名は一致する必要はありませんが、データ型とパラメーターの位置は一致する必要があります。ただし、@ListPrice = variable が使用されている場合を除きます。The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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)))+'.';  

次に結果セットの一部を示します。Here is the partial result set:

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.

G.G. テーブル値パラメーターを使用するUsing a Table-Valued Parameter

次の例では、テーブル値パラメーターの型を使用して、テーブルに複数の行を挿入します。The following example uses a table-valued parameter type to insert multiple rows into a table. さらに、パラメーターの型を作成して、その型を参照するテーブル変数を宣言し、パラメーター一覧を入力して値をストアド プロシージャに渡します。The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. このストアド プロシージャでは、値を使用してテーブルに複数の行を挿入します。The stored procedure uses the values to insert multiple rows into a table.

/* 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 [AdventureWorks2012].[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   
    [AdventureWorks2012].[Person].[StateProvince];  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
H.H. OUTPUT カーソル パラメーターを使用するUsing an OUTPUT cursor parameter

次の例では、OUTPUT カーソル パラメーターを使用して、プロシージャに対してローカルになっているカーソルを、呼び出し側のバッチ、プロシージャ、またはトリガーに戻します。The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

まず、Currency テーブルに対してカーソルを宣言し、そのカーソルをオープンするプロシージャを作成します。First, create the procedure that declares and then opens a cursor on the Currency table:

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  

次に、ローカル カーソル変数を宣言し、ローカル変数にカーソルを割り当てるプロシージャを実行した後、カーソルから行を取り出すバッチを実行します。Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

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

ストアド プロシージャを使用したデータの変更Modifying Data by using a Stored Procedure

このセクションの例では、プロシージャの定義にデータ操作言語 (DML) ステートメントを含めることで、テーブルまたはビューのデータを挿入または変更する方法を示します。Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. UPDATE をストアド プロシージャで使用するUsing UPDATE in a stored procedure

次の例では、UPDATE ステートメントをストアド プロシージャで使用しています。The following example uses an UPDATE statement in a stored procedure. このプロシージャには、1 つの入力パラメーター @NewHours と 1 つの出力パラメーター @RowCount があります。The procedure takes one input parameter, @NewHours and one output parameter @RowCount. その @NewHours パラメーター値を UPDATE ステートメントで使用して、HumanResources.Employee テーブルの VacationHours 列を更新します。The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. 影響を受けた行数は、@RowCount 出力パラメーターを使用して、ローカル変数に返されます。The @RowCount output parameter is used to return the number of rows affected to a local variable. VacationHours に設定する値は、SET 句で CASE 式を使用して条件に応じて決定しています。A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. 従業員の給与が時給ベース (SalariedFlag = 0) である場合、VacationHours@NewHours で指定された値に現在の時間数を加算した値に設定されます。それ以外の場合は、VacationHours@NewHours で指定された値に設定されます。When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

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;  

エラー処理Error Handling

このセクションの例では、ストアド プロシージャの実行時に発生する可能性のあるエラーの処理方法を示します。Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. TRY...CATCH の使用Using TRY...CATCH

この例では、TRY...CATCH コンストラクトを使用して、ストアド プロシージャの実行中にキャッチしたエラーの情報を返します。The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.

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;  

プロシージャの定義の難読化Obfuscating the Procedure Definition

このセクションの例では、ストアド プロシージャの定義を難読化する方法を示します。Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. WITH ENCRYPTION オプションを使用するUsing the WITH ENCRYPTION option

次の例では、HumanResources.uspEncryptThis プロシージャを作成します。The following example creates the HumanResources.uspEncryptThis procedure.

A適用対象: SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017、SQL Database.Applies to: SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

次の例に示すように、WITH ENCRYPTION オプションを使用すると、システム カタログにクエリを実行したりメタデータ関数を使用したりするときにプロシージャの定義を難読化します。The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

sp_helptext を実行します。Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

以下に結果セットを示します。Here is the result set.

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

sys.sql_modules カタログ ビューに直接クエリを実行します。Directly query the sys.sql_modules catalog view:

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

以下に結果セットを示します。Here is the result set.

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

プロシージャの強制再コンパイルForcing the Procedure to Recompile

このセクションの例では、WITH RECOMPILE 句を使用して、プロシージャを実行するたびに再コンパイルするように強制します。Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. WITH RECOMPILE オプションを使用するUsing the WITH RECOMPILE option

WITH RECOMPILE 句は、プロシージャに指定するパラメーターが定型的でない場合や、新しい実行プランをメモリにキャッシュまたは保存したくない場合に役立ちます。The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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;  

セキュリティ コンテキストの設定Setting the Security Context

このセクションの例では、EXECUTE AS 句を使用して、ストアド プロシージャが実行されるセキュリティ コンテキストを設定します。Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. EXECUTE AS 句を使用するUsing the EXECUTE AS clause

次の例では、EXECUTE AS 句を使用して、プロシージャを実行できるセキュリティ コンテキストを指定します。The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. この例では、オプションの CALLER により、プロシージャを呼び出したユーザーのコンテキストでプロシージャを実行できることを指定します。In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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  

N.N. カスタム権限セットを作成するCreating custom permission sets

次の例では、EXECUTE AS を使用して、データベース操作に対するカスタム権限を作成します。The following example uses EXECUTE AS to create custom permissions for a database operation. TRUNCATE TABLE など、許可できる権限のない操作もあります。Some operations such as TRUNCATE TABLE, do not have grantable permissions. TRUNCATE TABLE ステートメントをストアド プロシージャ内に組み込み、テーブルを変更する権限が許可されているユーザーとしてそのプロシージャを実行するように指定すると、テーブルの切り捨てを行うための権限を、そのプロシージャの EXECUTE 権限が許可されたユーザーに拡張できます。By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

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

例: Azure SQL データ ウェアハウスAzure SQL Data Warehouse および Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL データ ウェアハウスAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

O. O. SELECT ステートメントを実行するストアド プロシージャを作成します。Create a Stored Procedure that runs a SELECT statement

この例では、プロシージャを作成して実行するための基本構文を示します。This example shows the basic syntax for creating and running a procedure. バッチを実行する場合は、CREATE PROCEDURE を最初のステートメントにする必要があります。When running a batch, CREATE PROCEDURE must be the first statement. たとえば、AdventureWorksPDW2012AdventureWorksPDW2012 で次のストアド プロシージャを作成するには、最初にデータベース コンテキストを設定した後、CREATE PROCEDURE ステートメントを実行します。For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  
  
--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  
GO
  
--Show 10 Top Resellers  
EXEC Get10TopResellers;  

参照See Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
フロー制御言語 (TRANSACT-SQL) Control-of-Flow Language (Transact-SQL)
カーソル Cursors
データ型 (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
実行 AS (& a) #40 です。TRANSACT-SQL と #41 です。 EXECUTE AS (Transact-SQL)
ストアド プロシージャ (データベース エンジン) Stored Procedures (Database Engine)
sp_procoption (Transact-SQL) sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures (Transact-SQL) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
ストアド プロシージャの作成 Create a Stored Procedure
テーブル値パラメーターの使用 (データベース エンジン) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)