ストアドプリシーじゃの書き方を毎回思い出しているので、メモとして記載しておきます。

動作確認は SQL Server 2005 Development Edition で行っています。

1. ストアドプロシージャ定義テンプレート

1.1 パラメタなしプロシージャ

パラメタのないストアドプロシージャを作成します。最初にストアドプロシージャが定義している場合は作成前にDROP文で削除しています。

-- データベース名を指定する場合
USE [Component];
GO

-- 基本的なストアドプロシージャテンプレート
IF OBJECT_ID(N'[dbo].[usp_sampleprocedure]') IS NOT NULL
  DROP PROCEDURE [dbo].[usp_sampleprocedure];
GO

CREATE PROCEDURE [dbo].[usp_sampleprocedure] 
AS
BEGIN
  SET NOCOUNT ON;

  SELECT COUNT(*) FROM [dbo].[Article];
END
GO

SQL Server Management Studio 上でストアドを実行する場合は次のように実行します。

EXEC dbo.usp_sampleprocedure

1.2パラメタつきプロシージャ

 パラメタつきプロシージャを作成します。パラメタが指定されなかった場合のデフォルト値と、ストアド実行結果が値が設定されるOUTPUTを使用した例も同時に掲載します。ArticleCountパラメタがストアドを呼び出した結果値が設定されるパラメタであり、@CategoryIDのパラメタ定義で既定値を設定しています。

-- 基本的なストアドプロシージャテンプレート
IF OBJECT_ID(N'[dbo].[usp_sampleprocedure]') IS NOT NULL
  DROP PROCEDURE [dbo].[usp_sampleprocedure];
GO

CREATE PROCEDURE [dbo].[usp_sampleprocedure] 
     @PublishDate datetime
    ,@CategoryID int = 0
    ,@ArticleCount int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  
  IF @CategoryID = 0 
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
    END
  ELSE
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
         AND CategoryID = @CategoryID
    END
  
   SELECT @ArticleCOunt = @@ROWCOUNT
END

SSMS上で実行する場合は次のように呼び出します。

DECLARE @ArticleCount int
EXEC dbo.usp_sampleprocedure '2009/01/02', 1, @ArticleCount OUTPUT
PRINT @ArticleCount

1.3 ストアドを修正する

ストアドプロシージャを修正する場合は、ALTER文を使用します。

-- ストアドを修正する場合 ALTERを使用します。
ALTER PROCEDURE [dbo].[usp_sampleprocedure] 
     @PublishDate datetime
	,@CategoryID int = 0
    ,@ArticleCount int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  
  IF @CategoryID = 0 
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
    END
  ELSE
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
         AND CategoryID = @CategoryID
    END
  
   SELECT @ArticleCOunt = @@ROWCOUNT
END

1.4 ストアドプロシージャを削除する

ストアドを修正するにはDROP文を使用します。

DROP PROCEDURE [dbo].[usp_sampleprocedure]

2. ストアド便利情報

2.1 SQL Server 起動時に自動的にプロシージャを実行する

sp_procoptionを使用します。Books Online を確認すれば分かりますが、指定できるオプションはstartupのみです。

-- SQL Server が起動するときに自動実行するストアドを定義する
USE [master]
GO

sp_procoption @ProcName =  '[dbo].[usp_startupprocedure]' 
        , @OptionName = 'startup' 
        , @OptionValue = 'true'

2.2 ストアドやVIEWの定義SQLを取得する

sys.sql_modules カタログビューを使用するとストアドやVIEW,関数などの定義情報などが取得できます。サンプルでは、sys.objectsカタログビューを使用してオブジェクトのタイプ(関数,VIEWなど)情報も表示しています。

SELECT o.name, o.type, o.type_desc, m.definition, m.*
      FROM sys.sql_modules m
INNER JOIN sys.objects o
        ON m.object_id = o.object_id

2.3 ストアドを列挙する

sys.objects カタログビューを使用して関数や、ビューを列挙することができます。

-- ストアドを列挙する
SELECT name
  FROM sys.objects
 WHERE type = 'P'

2.4 ストアドの定義内容を表示する

sp_helptextを使用するとユーザー定義のルール、既定値、暗号化されていない Transact-SQL ストアド プロシージャ、ユーザー定義の Transact-SQL 関数、トリガ、計算列、CHECK 制約、ビュー、またはシステム ストアド プロシージャなどのシステム オブジェクトの定義を表示することができます。

EXEC sp_helptext 'usp_articleUpdateComment'

ストアドの定義情報を見られないようにするには暗号化をします。暗号化するにはストアド定義時にWITH ENCRYPTION を指定します。

-- ストアドを暗号化する
CREATE PROCEDURE usp_articleUpdate
WITH ENCRYPTION
AS
  UPDATE ...
GO

2.5　ストアド実行権限を付与する

ストアドの実行権限を使用するにはGRANT EXEC を使用します。下のクエリでは、someuserユーザにストアドの実行権限を付与しています。someuserがテーブルのアクセス権がなくとも、ストアドを実行できるようになります。ただし、TRUNCATE TABLEをストアド内で使用している場合は、TRUNCATE TABLE権限がない場合、エラーが発生します。TRUNCATE TABLEの場合に必要な権限はBOOKS Online を参照してください。

GRANT EXEC ON usp_articleUpdateComment TO someuser

特定のユーザにストアドのEXECUTE権限を付与するSQLを作成するには次の用になります。

SELECT 'GRANT EXEC ON ' + name + 'TO someuser'
  FROM sys.objects
 WHERE type = 'P'

2.6 ストアドプロシージャの実行ユーザのセキュリティコンテキストを指定する

ストアドプロシージャ定義時にEXECUTE AS {CALLER | SELF | OWNER | 'user_name'} を指定すると、ストアドプロシージャ実行時のセキュリティコンテキストを指定できます。既定はCALLERなので、ストアドの呼び出しユーザのセキュリティコンテキスト上で実行されます。CALLER の場合、sp_execsqlなど、動的SQLを使用してSELECTしている場合、EXECUTE権限だけでなく、テーブルのSELECT権限なども必要になります。EXECUTE AS オプションを指定することで、ストアドの実行時セキュリティコンテキストをownerやとくていのユーザに指定できます。

以下の例では、ONWERを指定しています。

CREATE PROCEDURE [dbo].[usp_sampleprocedure] 
     @PublishDate datetime
	,@CategoryID int = 0
    ,@ArticleCount int OUTPUT
WITH EXECUTE AS OWNER
AS
BEGIN
  SET NOCOUNT ON;
  
  IF @CategoryID = 0 
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
    END
  ELSE
    BEGIN
      DELETE dbo.TestTable
       WHERE PublishDate < @PublishDate
         AND CategoryID = @CategoryID
    END
  
   SELECT @ArticleCOunt = @@ROWCOUNT
END

2.7 ストアドプロシージャのキャッシュされた実行計画をクリアする

SQL Server 全体のキャッシュをクリアする場合は DBCC FREEPROCCACHE を使用します。キャッシュされているクエリの実行計画を表示する場合は、 sys.dm_exec_cached_plans 動的管理ビューに大してSELECTを発行します。

select *
  from sys.dm_exec_cached_plans

CREATE PROCEDURE文でWITH RECOMPILE を指定するとクエリの実行計画がキャッシュされないようになります。

3. おわり

説明は以上です。間違い等があればご指摘ください。