ストアド プロシージャの実行 (データベース エンジン)
ストアド プロシージャを実行するには、Transact-SQL EXECUTE ステートメントを使用します。または、ストアド プロシージャがバッチ内の最初のステートメントである場合は、EXECUTE キーワードを使用せずにストアド プロシージャを実行できます。
システム ストアド プロシージャの実行
システム ストアド プロシージャは、sp_ という文字で始まります。物理的にはリソース データベースに格納されますが、論理的には SQL Server のインスタンスにある、各システム データベースとユーザー定義データベースの sys スキーマに表示されます。システム ストアド プロシージャは、ストアド プロシージャ名を完全に修飾する必要はなく、どのデータベースからでも実行できます。スキーマ修飾されていない名前とは、sp_someproc のように 1 つの要素で構成される名前か、somedb..sp_someproc のように 2 番目の要素 (スキーマ名) が指定されていない 3 つの要素で構成される名前です。
名前の競合を回避するためには、すべてのシステム ストアド プロシージャ名を sys スキーマ名でスキーマ修飾することをお勧めします。次の例は、システム ストアド プロシージャの実行に関して推奨されている方法を示しています。
EXEC sys.sp_who;
次の例は、システム ストアド プロシージャの実行に関して、旧バージョンと互換性のある方法を示しています。
.gif "注意") 注 |
|---|
次のシステム ストアド プロシージャの実行方法は、今後のバージョンの SQL Server で廃止される予定です。新しい開発作業ではこれらの方法の使用を避け、現在この方法を使用しているアプリケーションは変更を検討してください。 |
EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;
データベースの照合順序の一致
SQL Server 2008 では、システム プロシージャ名を照合するときに、呼び出し元のデータベースの照合順序が使用されます。そのため、アプリケーションでは、システム プロシージャ名の大文字と小文字を常に区別する必要があります。たとえば、次のコードは、大文字と小文字を区別する照合順序が指定されたデータベースのコンテキストで実行された場合は失敗します。
exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help
正確なシステム ストアド プロシージャ名を表示するには、sys.system_objects カタログ ビューおよび sys.system_parameters カタログ ビューを使用します。
システム拡張ストアド プロシージャの実行
システム拡張ストアド プロシージャは、xp_ という文字で始まります。物理的にはリソース データベースに格納されますが、論理的には SQL Server のインスタンスにある、各システム データベースとユーザー定義データベースの sys スキーマに表示されます。次の例は、システム拡張ストアド プロシージャの実行に関して推奨されている方法を示しています。
EXEC sys.xp_subdirs 'c:\';
ユーザー定義のストアド プロシージャの実行
バッチ内、またはユーザー定義のストアド プロシージャや関数などのモジュール内のいずれかにある、ユーザー定義のストアド プロシージャを実行する場合、ストアド プロシージャ名を少なくともスキーマ名で修飾することを強くお勧めします。
次の例は、ユーザー定義のストアド プロシージャを実行するために推奨されている方法を示しています。
USE AdventureWorks2008R2;
GO
EXEC dbo.uspGetEmployeeManagers 50;
または
EXEC AdventureWorks2008R2.dbo.uspGetEmployeeManagers 50;
GO
修飾されていないユーザー定義のストアド プロシージャを指定した場合、データベース エンジンでは次の順序でプロシージャが検索されます。
現在のデータベースの sys スキーマ。
バッチまたは動的 SQL で実行された場合は、呼び出し側の既定のスキーマ。または、別のプロシージャ定義の本文の中に非修飾型プロシージャ名がある場合は、そのプロシージャを含んでいるスキーマが次に検索されます。既定のスキーマの詳細については、「ユーザーとスキーマの分離」を参照してください。
現在のデータベースにある dbo スキーマ。
.gif "重要な注意事項") 重要 |
|---|
ユーザーが作成したストアド プロシージャとシステム ストアド プロシージャの名前が同じである場合に、スキーマ以外の修飾名参照を使用していると、ユーザーが作成したストアド プロシージャは実行されません。詳細については、「ストアド プロシージャの作成 (データベース エンジン)」を参照してください。 |
パラメーターの指定
パラメーター値を指定できるようにストアド プロシージャが記述されていれば、パラメーター値を指定できます。
指定する値は、定数または変数である必要があります。パラメーター値には関数名は指定できません。変数には、ユーザー定義変数や @@spid などのシステム変数を使用できます。
次の例では、パラメーター値をストアド プロシージャ uspGetWhereUsedProductID に渡す方法を示しています。プロシージャには、製品 ID と日付の 2 つの入力パラメーターの値が渡されます。この例では、定数および変数としてパラメーターを渡す方法を示しています。また、変数を使用して関数の値を渡す方法も示しています。
USE AdventureWorks2008R2;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
ストアド プロシージャの定義と異なる順序でパラメーターを指定する場合は、パラメーターに名前を付ける必要があります。詳細については、「パラメータ名の指定」を参照してください。
パラメーターから呼び出し元のプログラムに値を返すように指定するには、OUTPUT キーワードを使用します。詳細については、「パラメータの方向の指定」を参照してください。
パラメーターの順序の指定
**@parameter =**value という形式でパラメーターを指定すると、パラメーターを任意の順序で指定することができます。また、既定値が与えられているパラメーターは省略することもできます。1 つのパラメーターを **@parameter =value の形式で指定した場合は、後続のパラメーターもすべてこの形式で指定する必要があります。@parameter =**value の形式を使用しない場合は、CREATE PROCEDURE ステートメントで定義された順番でパラメーターを指定する必要があります。
ストアド プロシージャを実行する場合、プロシージャの作成時にパラメーターの一覧に含まれていないパラメーターは、サーバーに拒否されます。参照渡し (パラメーター名を明示的に渡すこと) のパラメーターは、パラメーター名が一致しないと受け入れられません。
パラメーターでの既定値の使用
既定値が指定されているパラメーターは省略できますが、パラメーターの一覧を切り捨てることしかできません。たとえば、ストアド プロシージャに 5 つのパラメーターがある場合、4 番目と 5 番目のパラメーターを両方とも省略することはできますが、**@parameter =**value の形式でパラメーターを指定しない限り、4 番目のパラメーターだけを省略して 5 番目のパラメーターを指定することはできません。
パラメーターの既定値は、ストアド プロシージャ内でパラメーターに定義されていると、次の場合に使用できます。
ストアド プロシージャの実行時にパラメーターの値が指定されていない場合
パラメーターの値として DEFAULT キーワードが指定されている場合