SQL Server ストアド プロシージャ 作成の変更点

• 追加された行はこのように表示されます。
• 削除された行はこのように表示されます。

!!!SQL Server ストアド プロシージャ 作成
[SQL Server][SQL Server 2005][SQL Server ストアド プロシージャ][Transact-SQL]
!!!作成
!![CREATE PROCEDURE|http://msdn.microsoft.com/ja-jp/library/ms187926(v=SQL.90).aspx]
*作成には、Transact-SQL ステートメントの CREATE PROCEDURE を使用する
!注意
*CREATE PROCEDURE ステートメントは、1 つのバッチ内で他の SQL ステートメントと組み合わせて使用できない
*データベースに対して CREATE PROCEDURE 権限、スキーマに対して ALTER 権限を所持している必要があります。
*CLR ストアド プロシージャの場合は、<method_specifier> で参照されるアセンブリを所有しているか、そのアセンブリの REFERENCES 権限を所持している必要があります。
*スキーマ スコープが設定されたオブジェクトで、その名前は識別子の規則に従う必要があります。
*現在のデータベースにのみ作成することができる。
!!指定内容
*任意の入力パラメータと、出力パラメータ。
*プログラミング ステートメント。
*ステータス値。
*エラー処理ステートメント。 
**[ストアド プロシージャで指定できる、ERROR_LINE や ERROR_PROCEDURE などの、新しいエラー処理関数が導入されました。|http://msdn.microsoft.com/ja-jp/library/ms179296(v=SQL.90).aspx]
!命名
""sp_ をプレフィックスに使用してストアド プロシージャを作成しないこと。SQL Server では、システム ストアド プロシージャを指定するために sp_ プレフィックスを使用しています。独自のプロシージャ名がシステム プロシージャ名と競合していると、その名前は独自のプロシージャではなく、システム プロシージャにバインドされるので、アプリケーションの機能が停止することになります。
!一時ストアド プロシージャ
*一時テーブルと同様に、プロシージャ名にプレフィックスをつけて作成できる
*SQL Server がシャットダウンされるまで存在します。
*CLR ストアド プロシージャを一時ストアド プロシージャとして作成することはできません。

,一時ストアド プロシージャ,プレフィックス
,プライベート一時ストアド プロシージャ,#
,グローバル一時ストアド プロシージャ,##
!!例
[ サンプルデータベースの入手とインストール|SQL Server サンプルデータベースの入手とインストール]

!パラメータを使用しない
 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|http://msdn.microsoft.com/ja-jp/library/ms190669(v=SQL.90).aspx]
 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 製品 (バイク) の一覧
{{ref_image sqlsvr_sample10.jpg}}
*メッセージ
{{ref_image sqlsvr_sample11.jpg}}

*$300 より安い Adventure Works 製品 (バイク) の一覧
{{ref_image sqlsvr_sample12.jpg}}
*メッセージ
{{ref_image sqlsvr_sample13.jpg}}

!!参考

![SET NOCOUNT|http://msdn.microsoft.com/ja-jp/library/ms189837(v=SQL.90).aspx]
*ON の場合、行数は返されません。SET NOCOUNT が OFF の場合、行数が返されます。
OON の場合でも、@@ROWCOUNT 関数は更新されます。
*ON の場合でも、@@ROWCOUNT 関数は更新されます。
*実際に返すデータが少量のステートメントで構成されるストアド プロシージャの場合、ネットワーク通信量が大きく減少するので、SET NOCOUNT を ON に設定するとパフォーマンスが大きく向上します。