[アーティクル]
10/31/2012

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

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

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

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

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

任意の入力パラメーターと、呼び出し側のプロシージャまたはバッチへの出力パラメーター。
他のプロシージャの呼び出しなど、データベース内での処理を実行するプログラミングステートメント。
呼び出し側のプロシージャまたはバッチに返され、成功、失敗、および失敗の原因を示すステータス値。
発生する可能性のあるエラーをキャッチし、処理するために必要なエラー処理ステートメント。

ERROR_LINE や ERROR_PROCEDURE などのエラー処理関数を、ストアドプロシージャで指定できます。詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。

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

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

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

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
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 に直接作成したプロシージャは、接続の作成が終了した後も存在します。

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

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

例

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

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

USE AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
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 AdventureWorks2008R2;
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,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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 AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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 パラメーターを使用する例を示しています。外部プロシージャ、バッチ、または複数の Transact-SQL ステートメントは、OUTPUT パラメーターを使用して、プロシージャの実行中に設定された値にアクセスできます。

USE AdventureWorks2008R2;
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 を使用して、[メッセージ] ウィンドウにメッセージを返します。

注
プロシージャの作成中および変数の使用中に、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.

次の方法で共有

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

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

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

例

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

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

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

D. OUTPUT パラメーターの使用

関連項目

タスク

参照

概念

その他の技術情報

その他のリソース

次の方法で共有

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

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

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

例

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

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

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

D. OUTPUT パラメーターの使用

関連項目

タスク

参照

概念

その他の技術情報

その他のリソース

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

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

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

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