ストアド プロシージャの作成 (データベース エンジン)

  • [アーティクル]

ストアド プロシージャの作成には、Transact-SQL ステートメントの CREATE PROCEDURE を使用します。

ストアド プロシージャを作成する前に、次の点に注意してください。

  • CREATE PROCEDURE ステートメントは、1 つのバッチ内で他の SQL ステートメントと組み合わせて使用することはできません。
  • プロシージャを作成するには、データベースに対して CREATE PROCEDURE 権限、プロシージャが作成されるスキーマに対して ALTER 権限を所持している必要があります。CLR ストアド プロシージャの場合は、<method_specifier> で参照されるアセンブリを所有しているか、そのアセンブリの REFERENCES 権限を所持している必要があります。
  • ストアド プロシージャはスキーマ スコープが設定されたオブジェクトで、その名前は識別子の規則に従う必要があります。
  • ストアド プロシージャは現在のデータベースにのみ作成することができます。

ストアド プロシージャを作成する場合、次の事項を指定する必要があります。

  • 任意の入力パラメータと、呼び出し側のプロシージャまたはバッチへの出力パラメータ。
  • 他のプロシージャの呼び出しなど、データベース内での処理を実行するプログラミング ステートメント。
  • 呼び出し側のプロシージャまたはバッチに返され、成功、失敗、および失敗の原因を示すステータス値。
  • 発生する可能性のあるエラーをキャッチし、処理するために必要なエラー処理ステートメント。
    Microsoft SQL Server 2005 には、ストアド プロシージャで指定できる、ERROR_LINE や ERROR_PROCEDURE などの、新しいエラー処理関数が導入されました。詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。

ストアド プロシージャの命名

sp_ をプレフィックスに使用してストアド プロシージャを作成しないことをお勧めします。SQL Server では、システム ストアド プロシージャを指定するために sp_ プレフィックスを使用しています。選択した名前が、今後のシステム プロシージャと競合する可能性があります。アプリケーションでスキーマ以外の修飾名参照が使用されていて、独自のプロシージャ名がシステム プロシージャ名と競合していると、その名前は独自のプロシージャではなく、システム プロシージャにバインドされるので、アプリケーションの機能が停止することになります。

システム ストアド プロシージャと同じ名前のユーザー定義のストアド プロシージャ、および修飾されていないか、dbo スキーマにあるユーザー定義ストアド プロシージャは実行されません。システム ストアド プロシージャが代わりに実行されます。次の例でその動作を示します。

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

明示的なスキーマ修飾子を使用することで、わずかにパフォーマンスも向上します。データベース エンジンが複数のスキーマを検索してプロシージャを見つける必要がない場合は、名前の解決が若干速くなります。詳細については、「ストアド プロシージャの実行」を参照してください。

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

プライベート一時ストアド プロシージャとグローバル一時ストアド プロシージャは、一時テーブルと同様に、プロシージャ名にプレフィックスとして # と ## を付けて作成できます。# は、ローカル一時ストアド プロシージャを表します。## は、グローバル一時ストアド プロシージャを表します。一時ストアド プロシージャは、SQL Server がシャットダウンされるまで存在します。

一時ストアド プロシージャは、Transact-SQL ステートメントやバッチの実行プランの再利用をサポートしていない、旧バージョンの SQL Server に接続する際に便利です。SQL Server 2000 以降に接続するアプリケーションでは、一時ストアド プロシージャの代わりにシステム ストアド プロシージャ sp_executesql を使用する必要があります。ローカル一時プロシージャを実行できるのは、そのプロシージャを作成した接続のみです。接続が閉じられると、ローカル一時プロシージャは自動的に削除されます。

グローバル一時ストアド プロシージャは、すべての接続で実行できます。グローバル一時ストアド プロシージャは、そのプロシージャを作成したユーザーが使用していた接続が閉じられ、他のすべての接続で現在実行中の同じバージョンのプロシージャが完了するまで存在します。プロシージャの作成に使用した接続が閉じられると、それ以降はそのグローバル一時ストアド プロシージャを実行することはできなくなります。既にプロシージャの実行を開始している接続だけは、そのストアド プロシージャの実行を完了させることができます。

プレフィックスとして # または ## が付いていないストアド プロシージャを tempdb データベースに直接作成した場合、そのストアド プロシージャは、SQL Server がシャットダウンされたときに自動的に削除されます。これは、SQL Server を起動するたびに tempdb が作成し直されるためです。tempdb に直接作成したプロシージャは、接続の作成が終了した後も存在します。

ms190669.note(ja-jp,SQL.90).gifメモ :
一時ストアド プロシージャを過剰に使用すると、tempdb のシステム テーブルで競合が発生し、パフォーマンスに影響することがあります。代わりに sp_executesql を使用することをお勧めします。sp_executesql はシステム テーブルにデータを保存しないので、この問題は発生しません。

CLR ストアド プロシージャを一時ストアド プロシージャとして作成することはできません。

例

A. 単純なプロシージャと複雑な SELECT の併用

次のストアド プロシージャは、ビューからすべての従業員 (フルネーム)、役職名、部署名を返します。このストアド プロシージャはパラメータをまったく使用しません。

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

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

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

B. パラメータを指定した単純なプロシージャの使用

次のストアド プロシージャは、指定した従業員 (フルネーム)、役職名、部署名のみをビューから返します。このストアド プロシージャは、渡されたパラメータと完全に一致するものを受け入れます。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

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

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

C. ワイルドカード パラメータを指定した単純なプロシージャの使用

次のストアド プロシージャは、指定した従業員 (フルネーム)、役職名、部署名のみをビューから返します。このストアド プロシージャは、渡されたパラメータのパターン検索を行いますが、パラメータが指定されない場合は、あらかじめ設定された既定値 (文字 D で始まる姓) を使用します。

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

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

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

D. OUTPUT パラメータの使用

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

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

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

ms190669.note(ja-jp,SQL.90).gifメモ :
プロシージャの作成中および変数の使用中に、OUTPUT 変数を定義する必要があります。パラメータ名と変数名は一致する必要はありませんが、データ型とパラメータの位置は一致する必要があります (ただし、@ListPrice= variable が使用されている場合を除きます)。 
DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

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

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

参照

処理手順

ストアド プロシージャを作成する方法 (SQL Server Management Studio)

概念

ストアド プロシージャとトリガでのロールバックとコミット
CLR ストアド プロシージャの作成
ストアド プロシージャの実行 (データベース エンジン)
ストアド プロシージャの変更
ストアド プロシージャの再コンパイル
ストアド プロシージャの表示
ストアド プロシージャの削除

その他の技術情報

CREATE PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS 句 (Transact-SQL)
REVERT (Transact-SQL)
コンテキストの切り替え
パラメータの指定
ストアド プロシージャからデータを返す処理

ヘルプおよび情報

SQL Server 2005 の参考資料の入手