SQL Server ストアド プロシージャ 作成

SQL Server ストアド プロシージャ 作成

[SQL Server][SQL Server 2005][SQL Server ストアド プロシージャ][Transact-SQL]

作成

 CREATE PROCEDURE

• 作成には、Transact-SQL ステートメントの CREATE PROCEDURE を使用する

注意

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

 指定内容

• 任意の入力パラメータと、出力パラメータ。
• プログラミング ステートメント。
• ステータス値。
• エラー処理ステートメント。
  • ストアド プロシージャで指定できる、ERROR_LINE や ERROR_PROCEDURE などの、新しいエラー処理関数が導入されました。

命名

sp_ をプレフィックスに使用してストアド プロシージャを作成しないこと。SQL Server では、システム ストアド プロシージャを指定するために sp_ プレフィックスを使用しています。独自のプロシージャ名がシステム プロシージャ名と競合していると、その名前は独自のプロシージャではなく、システム プロシージャにバインドされるので、アプリケーションの機能が停止することになります。

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

• 一時テーブルと同様に、プロシージャ名にプレフィックスをつけて作成できる
• SQL Server がシャットダウンされるまで存在します。
• CLR ストアド プロシージャを一時ストアド プロシージャとして作成することはできません。

 例

サンプルデータベースの入手とインストール

パラメータを使用しない

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

実行

use AdventureWorks;
go
execute HumanResources.uspGetAllEmployees;
go

execute は、exec と省略することも、完全に省略することも可能

パラメータを指定する

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

実行

use AdventureWorks;
go
execute HumanResources.uspGetEmployees N'Ackerman',N'Pilar';
go
-- Or
execute HumanResources.uspGetEmployees @LastName=N'Ackerman',@FirstName=N'Pilar';
go

ワイルドカード パラメータを指定

use AdventureWorks;
go
if object_id( 'HumanResouces.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

実行

様々な組み合わせで実行できる

use AdventureWorks;
go
execute HumanResources.uspGetEmployees2;
go
execute HumanResources.uspGetEmployees2 N'Wi%'
go
execute HumanResources.uspGetEmployees2 N'[DC]%'; 
go
execute HumanResources.uspGetEmployees2 N'H%', N'S%';
go

OUTPUT パラメータの使用

OUTPUT

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 output
as
    set nocount on;
    select p.[Name] as Product, p.ListPrice as 'List Price'
    from Production.Product as p
        inner join Production.ProductSubcategory as s
        on p.ProductSubcategoryID = s.ProductSubcategoryID
    where s.[Name] like @Product 
    and   p.ListPrice < @MaxPrice;
set @ListPrice = (select max(p.ListPrice)
                    from Production.Product as p
                        inner join Production.ProductSubcategory as s
                        on p.ProductSubcategoryID = s.ProductSubcategoryID
                    where s.[Name] like @Product
                    and   p.ListPrice < @MaxPrice );
set @ComparePrice = @MaxPrice;
go

実行

プロシージャの作成中および変数の使用中に、OUTPUT 変数を定義する必要があります。パラメータ名と変数名は一致する必要はありませんが、データ型とパラメータの位置は一致する必要があります

use AdventureWorks;
go
declare @ComparePrice money, @Cost money
execute Production.uspGetList '%Bikes%', 700,
    @ComparePrice output,
    @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)))+'.'

• $700 より安い Adventure Works 製品 (バイク) の一覧

• メッセージ

• $300 より安い Adventure Works 製品 (バイク) の一覧

• メッセージ

 参考

SET NOCOUNT

• ON の場合、行数は返されません。SET NOCOUNT が OFF の場合、行数が返されます。
• ON の場合でも、@@ROWCOUNT 関数は更新されます。
• 実際に返すデータが少量のステートメントで構成されるストアド プロシージャの場合、ネットワーク通信量が大きく減少するので、SET NOCOUNT を ON に設定するとパフォーマンスが大きく向上します。