ALTER PROCEDURE (Transact-SQL)

[アーティクル]
03/07/2014

SQL Server で、CREATE PROCEDURE ステートメントを使用して作成した既存のプロシージャを変更します。

適用対象: SQL Server (SQL Server 2008 から現在のバージョンまで)、Windows Azure SQL データベース (初回のリリースから現在のバージョンまで)

トピックリンクアイコン Transact-SQL 構文表記規則 (Transact-SQL)

構文

--SQL Server Syntax
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--SQL Server CLR Stored Procedure Syntax
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

-- Windows Azure SQL Database Syntax 

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [type_schema_name. ] data_type } 
    [ VARYING ] [= default ] [ [ OUT [ PUT ] 
    ] [,...n ] 
[ WITH <procedure_option> [ , ...n ] ] 
AS 
     { <sql_statement> [...n ] }
[;]

<procedure_option> ::= 
    [ RECOMPILE ] 
    [ EXECUTE_AS_Clause ] 

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

引数

schema_name
プロシージャが属するスキーマの名前を指定します。
procedure_name
変更するプロシージャの名前を指定します。プロシージャ名は、識別子のルールに従っている必要があります。
; number
同じ名前のプロシージャをグループ化するために使用する既存の整数を指定します (省略可能)。グループ化されたプロシージャは、DROP PROCEDURE ステートメントを使用して一度に削除できます。

注意

この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、この機能の使用を避け、現在この機能を使用しているアプリケーションは修正するようにしてください。
@ parameter
プロシージャ内のパラメーターを指定します。パラメーターは 2,100 個まで指定できます。
[ type_schema_name**.**] data_type
パラメーターのデータ型とそれが属するスキーマを指定します。

データ型の制約については、「CREATE PROCEDURE (Transact-SQL)」を参照してください。
VARYING
出力パラメーターとしてサポートされている結果セットを指定します。このパラメーターはストアドプロシージャによって動的に作成され、その内容は変化します。カーソルパラメーターにのみ適用されます。このオプションは、CLR プロシージャでは無効です。
default
パラメーターの既定値です。
OUT | OUTPUT
パラメーターが、戻りパラメーターであることを示します。
READONLY
パラメーターをプロシージャの本体内で更新または変更できないことを示します。パラメーターの型がテーブル値型の場合は、READONLY を指定する必要があります。
RECOMPILE
データベースエンジンでは、このプロシージャ用のプランをキャッシュせず、実行時にプロシージャを再コンパイルします。
ENCRYPTION

適用対象: SQL Server 2008 から SQL Server 2014。

データベースエンジンで、ALTER PROCEDURE ステートメントの元のテキストを、暗号化した形式に変換することを示します。暗号化した形式の出力は、SQL Server 内のどのカタログビューでも直接見ることはできません。システムテーブルまたはデータベースファイルへのアクセス権を持たないユーザーは、暗号化した形式のテキストを取得できません。 DAC ポート経由でシステムテーブルにアクセスする権限、または直接データベースファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。また、サーバープロセスにデバッガーをアタッチできるユーザーは、実行時、元のプロシージャをメモリから取得できます。システムメタデータへのアクセスの詳細については、「メタデータ表示の構成」を参照してください。

このオプションを使って作成したプロシージャを、SQL Server レプリケーションの一部として発行することはできません。

このオプションは、共通言語ランタイム (CLR) のストアドプロシージャには指定できません。

注意

データベースエンジンでは、アップグレード中に、sys.sql_modules に格納されている暗号化コメントにより、プロシージャが再作成されます。
EXECUTE AS
アクセス後にストアドプロシージャを実行するセキュリティコンテキストを指定します。

詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。
FOR REPLICATION

適用対象: SQL Server 2008 から SQL Server 2014。

レプリケーション用に作成したストアドプロシージャは、サブスクライバーでは実行できないことを示します。 FOR REPLICATION オプションを指定して作成したストアドプロシージャは、ストアドプロシージャフィルターとして使用され、レプリケーション時にのみ実行されます。 FOR REPLICATION を指定した場合、パラメーターは宣言できません。このオプションは、CLR プロシージャでは無効です。 RECOMPILE オプションは、FOR REPLICATION を使って作成されたプロシージャでは無視されます。

注意

このオプションは、包含データベースでは使用できません。
{ [ BEGIN ] sql_statement [;] [ ...n ][ END ] }
プロシージャの本体を構成する 1 つ以上の Transact-SQL ステートメントを指定します。省略可能な BEGIN キーワードと END キーワードを使用して、ステートメントを囲むことができます。詳細については、「CREATE PROCEDURE (Transact-SQL)」の「ベストプラクティス」、「全般的な解説」、および「制限事項と制約事項」を参照してください。
EXTERNAL NAME assembly_name**.class_name.**method_name

適用対象: SQL Server 2008 から SQL Server 2014。

CLR ストアドプロシージャで参照する .NET Framework アセンブリのメソッドを指定します。 class_name は、有効な SQL Server 識別子であること、およびアセンブリにクラスとして存在していることが必要です。クラス名に名前空間とその区切り文字のピリオド (.) が含まれる場合は、クラス名をかっこ ([ ]) または引用符 (" ") で区切る必要があります。指定するメソッドは、クラスの静的メソッドであることが必要です。

既定では、SQL Server は CLR コードを実行できません。共通言語ランタイムモジュールを参照するデータベースオブジェクトを作成、変更、および削除することはできますが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、sp_configure を使用します。

注意

CLR プロシージャは、包含データベースではサポートされていません。

全般的な解説

Transact-SQL ストアドプロシージャを CLR ストアドプロシージャに変更したり、その逆に変更することはできません。

ALTER PROCEDURE では権限は変更されず、従属ストアドプロシージャまたはトリガーに影響することはありませんが、 QUOTED_IDENTIFIER と ANSI_NULLS の現在のセッション設定は、変更時にストアドプロシージャに取り込まれます。ストアドプロシージャの最初の作成時に有効であった設定と変更後の設定が異なる場合、ストアドプロシージャの動作が変わる可能性があります。

以前のプロシージャ定義が WITH ENCRYPTION または WITH RECOMPILE を使用して作成されている場合、これらのオプションは、ALTER PROCEDURE に指定されるときだけ有効になります。

ストアドプロシージャの詳細については、「CREATE PROCEDURE (Transact-SQL)」を参照してください。

セキュリティ

権限

プロシージャの ALTER 権限、または db_ddladmin 固定データベースロールのメンバーシップが必要です。

使用例

次の例では、uspVendorAllInfo ストアドプロシージャを作成します。このプロシージャは、Adventure Works Cycles を提供するすべてのベンダーの名前と、そのベンダーの提供製品、信用格付け、およびベンダーが現時点で製品を提供できるかどうかを返します。このプロシージャを作成した後、別の結果セットを返すようプロシージャを変更します。

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

次の例では、uspVendorAllInfo ストアドプロシージャを変更します。 EXECUTE AS CALLER 句を削除し、指定した製品を供給するベンダーだけを返すようにプロシージャの本体を変更します。ここでは、LEFT 関数および CASE 関数を使用して、結果セットの表示をカスタマイズします。

USE AdventureWorks2012;
GO
ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SET NOCOUNT ON;
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Rating' = CASE v.CreditRating 
        WHEN 1 THEN 'Superior'
        WHEN 2 THEN 'Excellent'
        WHEN 3 THEN 'Above average'
        WHEN 4 THEN 'Average'
        WHEN 5 THEN 'Below average'
        ELSE 'No rating'
        END
    , Availability = CASE v.ActiveFlag
        WHEN 1 THEN 'Yes'
        ELSE 'No'
        END
    FROM Purchasing.Vendor AS v 
    INNER JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO

以下に結果セットを示します。

Vendor Product name Rating Availability

-------------------- ------------- ------- ------------

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)

次の方法で共有

ALTER PROCEDURE (Transact-SQL)

構文

引数

全般的な解説

セキュリティ

権限

使用例

関連項目

参照

概念

その他のリソース