| ページ一覧 | ブログ | twitter |  書式 | 書式(表) |

MyMemoWiki

「SQL Server ストアド プロシージャ 作成」の版間の差分

提供: MyMemoWiki
ナビゲーションに移動 検索に移動
 
(同じ利用者による、間の1版が非表示)
1行目: 1行目:
==SQL Server ストアド プロシージャ 作成==
+
==[[SQL Server ストアド プロシージャ 作成]]==
[[SQL Server]][[SQL Server 2005]][[SQL Server ストアド プロシージャ]][[Transact-SQL]]
+
[[SQL Server]] | [[SQL Server 2005]] | [[SQL Server ストアド プロシージャ]] | [[Transact-SQL]] |
 
==作成==
 
==作成==
 
===[http://msdn.microsoft.com/ja-jp/library/ms187926(v=SQL.90).aspx CREATE PROCEDURE]===
 
===[http://msdn.microsoft.com/ja-jp/library/ms187926(v=SQL.90).aspx CREATE PROCEDURE]===
*作成には、Transact-SQL ステートメントの CREATE PROCEDURE を使用する
+
*作成には、[[Transact-SQL]] ステートメントの CREATE PROCEDURE を使用する
 
====注意====
 
====注意====
*CREATE PROCEDURE ステートメントは、1 つのバッチ内で他の SQL ステートメントと組み合わせて使用できない
+
*CREATE PROCEDURE ステートメントは、1 つのバッチ内で他の [[SQL]] ステートメントと組み合わせて使用できない
*データベースに対して CREATE PROCEDURE 権限、スキーマに対して ALTER 権限を所持している必要があります。
+
*データベースに対して C[[R]]EATE P[[R]]OCEDU[[R]]E 権限、スキーマに対して ALTE[[R]] 権限を所持している必要があります。
*CLR ストアド プロシージャの場合は、<method_specifier> で参照されるアセンブリを所有しているか、そのアセンブリの REFERENCES 権限を所持している必要があります。
+
*CL[[R]] ストアド プロシージャの場合は、<method_specifier> で参照されるアセンブリを所有しているか、そのアセンブリの [[R]]EFE[[R]]ENCES 権限を所持している必要があります。
 
*スキーマ スコープが設定されたオブジェクトで、その名前は識別子の規則に従う必要があります。
 
*スキーマ スコープが設定されたオブジェクトで、その名前は識別子の規則に従う必要があります。
 
*現在のデータベースにのみ作成することができる。
 
*現在のデータベースにのみ作成することができる。
17行目: 17行目:
 
**[http://msdn.microsoft.com/ja-jp/library/ms179296(v=SQL.90).aspx ストアド プロシージャで指定できる、ERROR_LINE や ERROR_PROCEDURE などの、新しいエラー処理関数が導入されました。]
 
**[http://msdn.microsoft.com/ja-jp/library/ms179296(v=SQL.90).aspx ストアド プロシージャで指定できる、ERROR_LINE や ERROR_PROCEDURE などの、新しいエラー処理関数が導入されました。]
 
====命名====
 
====命名====
<blockquote>sp_ をプレフィックスに使用してストアド プロシージャを作成しないこと。SQL Server では、システム ストアド プロシージャを指定するために sp_ プレフィックスを使用しています。独自のプロシージャ名がシステム プロシージャ名と競合していると、その名前は独自のプロシージャではなく、システム プロシージャにバインドされるので、アプリケーションの機能が停止することになります。</blockquote>
+
<blockquote>sp_ をプレフィックスに使用してストアド プロシージャを作成しないこと。[[SQL Server]] では、システム ストアド プロシージャを指定するために sp_ プレフィックスを使用しています。独自のプロシージャ名がシステム プロシージャ名と競合していると、その名前は独自のプロシージャではなく、システム プロシージャにバインドされるので、アプリケーションの機能が停止することになります。</blockquote>
 
====一時ストアド プロシージャ====
 
====一時ストアド プロシージャ====
 
*一時テーブルと同様に、プロシージャ名にプレフィックスをつけて作成できる
 
*一時テーブルと同様に、プロシージャ名にプレフィックスをつけて作成できる
*SQL Server がシャットダウンされるまで存在します。
+
*[[SQL Server]] がシャットダウンされるまで存在します。
*CLR ストアド プロシージャを一時ストアド プロシージャとして作成することはできません。
+
*CL[[R]] ストアド プロシージャを一時ストアド プロシージャとして作成することはできません。
  
 
{|class="wikitable"
 
{|class="wikitable"
35行目: 35行目:
 
|}
 
|}
 
===例===
 
===例===
[[SQL Server サンプルデータベースの入手とインストール  サンプルデータベースの入手とインストール]]
+
[SQL Server サンプルデータベースの入手とインストール  サンプルデータベースの入手とインストール]
  
 
====パラメータを使用しない====
 
====パラメータを使用しない====
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  if object_id( 'HumanResources.uspGetAllEmployees', 'P' ) is not null
+
  if object_id( 'Human[[R]]esources.uspGetAllEmployees', 'P' ) is not null
     drop procedure HumanResources.uspGetAllEmployees;
+
     drop procedure Human[[R]]esources.uspGetAllEmployees;
 
  go
 
  go
  create procedure HumanResources.uspGetAllEmployees
+
  create procedure Human[[R]]esources.uspGetAllEmployees
 
  as
 
  as
 
     set nocount on;
 
     set nocount on;
 
     select LastName, FirstName, JobTitle, Department
 
     select LastName, FirstName, JobTitle, Department
     from HumanResources.vEmployeeDepartment;
+
     from Human[[R]]esources.vEmployeeDepartment;
 
  go
 
  go
 
=====実行=====
 
=====実行=====
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  execute HumanResources.uspGetAllEmployees;
+
  execute Human[[R]]esources.uspGetAllEmployees;
 
  go
 
  go
 
<blockquote>execute は、exec と省略することも、完全に省略することも可能</blockquote>
 
<blockquote>execute は、exec と省略することも、完全に省略することも可能</blockquote>
59行目: 59行目:
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  if object_id( 'HumanResources.uspGetEmployees', 'P' ) is not null
+
  if object_id( 'Human[[R]]esources.uspGetEmployees', 'P' ) is not null
     drop procedure HumanResources.uspGetEmployees;
+
     drop procedure Human[[R]]esources.uspGetEmployees;
 
  go
 
  go
  create procedure HumanResources.uspGetEmployees
+
  create procedure Human[[R]]esources.uspGetEmployees
 
     @LastName nvarchar(50),
 
     @LastName nvarchar(50),
 
     @FirstName nvarchar(50)
 
     @FirstName nvarchar(50)
68行目: 68行目:
 
     set nocount on;
 
     set nocount on;
 
     select FirstName, LastName, JobTitle, Department
 
     select FirstName, LastName, JobTitle, Department
     from HumanResources.vEmployeeDepartment
+
     from Human[[R]]esources.vEmployeeDepartment
 
     where FirstName = @FirstName and LastName = @LastName;
 
     where FirstName = @FirstName and LastName = @LastName;
 
  go
 
  go
74行目: 74行目:
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  execute HumanResources.uspGetEmployees N'Ackerman',N'Pilar';
+
  execute Human[[R]]esources.uspGetEmployees N'Ackerman',N'Pilar';
 
  go
 
  go
 
  -- Or
 
  -- Or
  execute HumanResources.uspGetEmployees @LastName=N'Ackerman',@FirstName=N'Pilar';
+
  execute Human[[R]]esources.uspGetEmployees @LastName=N'Ackerman',@FirstName=N'Pilar';
 
  go
 
  go
 
   
 
   
83行目: 83行目:
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  if object_id( 'HumanResouces.uspGetEmployees2','P' ) is not null
+
  if object_id( 'Human[[R]]esouces.uspGetEmployees2','P' ) is not null
     drop procedure HumanResources.uspGetEmployees2;
+
     drop procedure Human[[R]]esources.uspGetEmployees2;
 
  go
 
  go
  create procedure HumanResources.uspGetEmployees2
+
  create procedure Human[[R]]esources.uspGetEmployees2
 
     @LastName nvarchar(50) = N'D%',
 
     @LastName nvarchar(50) = N'D%',
 
     @FirstName nvarchar(50) = N'%'
 
     @FirstName nvarchar(50) = N'%'
92行目: 92行目:
 
     set nocount on;
 
     set nocount on;
 
     select FirstName, LastName, JobTitle, Department
 
     select FirstName, LastName, JobTitle, Department
     from HumanResources.vEmployeeDepartment
+
     from Human[[R]]esources.vEmployeeDepartment
 
     where FirstName like @FirstName and LastName like @LastName;
 
     where FirstName like @FirstName and LastName like @LastName;
 
  go
 
  go
99行目: 99行目:
 
  use AdventureWorks;
 
  use AdventureWorks;
 
  go
 
  go
  execute HumanResources.uspGetEmployees2;
+
  execute Human[[R]]esources.uspGetEmployees2;
 
  go
 
  go
  execute HumanResources.uspGetEmployees2 N'Wi%'
+
  execute Human[[R]]esources.uspGetEmployees2 N'Wi%'
 
  go
 
  go
 
  execute HumanResources.uspGetEmployees2 N'[DC]%';  
 
  execute HumanResources.uspGetEmployees2 N'[DC]%';  
 
  go
 
  go
  execute HumanResources.uspGetEmployees2 N'H%', N'S%';
+
  execute Human[[R]]esources.uspGetEmployees2 N'H%', N'S%';
 
  go
 
  go
  
167行目: 167行目:
 
====[http://msdn.microsoft.com/ja-jp/library/ms189837(v=SQL.90).aspx SET NOCOUNT]====
 
====[http://msdn.microsoft.com/ja-jp/library/ms189837(v=SQL.90).aspx SET NOCOUNT]====
 
*ON の場合、行数は返されません。SET NOCOUNT が OFF の場合、行数が返されます。
 
*ON の場合、行数は返されません。SET NOCOUNT が OFF の場合、行数が返されます。
*ON の場合でも、@@ROWCOUNT 関数は更新されます。
+
*ON の場合でも、@@[[R]]OWCOUNT 関数は更新されます。
 
*実際に返すデータが少量のステートメントで構成されるストアド プロシージャの場合、ネットワーク通信量が大きく減少するので、SET NOCOUNT を ON に設定するとパフォーマンスが大きく向上します。
 
*実際に返すデータが少量のステートメントで構成されるストアド プロシージャの場合、ネットワーク通信量が大きく減少するので、SET NOCOUNT を ON に設定するとパフォーマンスが大きく向上します。

2020年2月16日 (日) 04:32時点における最新版

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 権限を所持している必要があります。
  • スキーマ スコープが設定されたオブジェクトで、その名前は識別子の規則に従う必要があります。
  • 現在のデータベースにのみ作成することができる。

指定内容

命名

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

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

  • 一時テーブルと同様に、プロシージャ名にプレフィックスをつけて作成できる
  • 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

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

パラメータを指定する

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
実行

<blockquote>様々な組み合わせで実行できる</blockquote>

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
実行

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

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 製品 (バイク) の一覧

1128 sqlsvr sample10.jpg

  • メッセージ

1129 sqlsvr sample11.jpg

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

1130 sqlsvr sample12.jpg

  • メッセージ

1131 sqlsvr sample13.jpg

参考

SET NOCOUNT

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