SQL Server のストアドプロシージャを変更・削除する

前回「 SQL Server のストアドプロシージャを作成する」では、ユーザー定義ストアドプロシージャを作ってみました。

今回は、既存のーザー定義ストアドプロシージャを変更・削除してみましょう。

基本的にはユーザー定義関数の変更・削除と、 FUNCTION と PROCEDURE というキーワードが違う以外はほぼ同じです。

「 SQL Server のストアドプロシージャを作成する」で生成した、uspStudentDelete というストアドプロシージャが存在している前提で進めます。

ストアドプロシージャの変更

ユーザー定義のストアドプロシージャを変更するには ALTER PROCEDURE を使います。

シンプルなユーザー定義のストアドプロシージャを変更する構文は次の通りです。

CREATE PROCEDURE [ ストアドプロシージャ名 ]
    [ @入力パラメーター名 1 ] [ 入力パラメーター名 1 のデータ型 ],
    [ @入力パラメーター名 2 ] [ 入力パラメーター名 2 のデータ型 ],
    [ @入力パラメーター名 3 ] [ 入力パラメーター名 3 のデータ型 ],
    ..
AS
BEGIN

     [ ストアドプロシージャで処理したい一連の T-SQL ステートメント ]

END;

CREATE の代わりに ALTER というキーワードを使う以外は CREATE の時と同じです。

スクリプトを別でソース管理しているのでなければ、既存のストアドプロシージャを変更する時は、データベースに保存されているものを元に変更することも多いかと思います。

生成したユーザー定義のストアドプロシージャは、SQL Server Management Studio のプログラミング > ストアドプロシージャの箇所で見つけることができます。

変更したいユーザー定義ストアドプロシージャを選んで右クリックで [変更] を選択すると、次のように ALTER PROCEDURE のステートメントがクエリー画面に出てきます。

例えば、uspStudentDelete では、TestResult テーブルに StudentID のデータが存在すれば、アウトプットパラメータで「 TestResult が存在するので削除できません。」というメッセージを返していましたが、代わりに TestResultテーブルの、その StudentID のデータも削除してしまい、その際はアウトプットパラメータで「 TestResult のデータも削除しました。」と返すように変更してみましょう。

ALTER PROCEDURE [dbo].[uspStudentDelete]
    @StudentID      INT,
    @WarningMessage NVARCHAR(100) OUTPUT
AS
BEGIN

    IF EXISTS
        (SELECT *
         FROM   TestResult
         WHERE  StudentID = @StudentID)
    BEGIN

        DELETE
        FROM    TestResult
        WHERE   StudentID = @StudentID;

        SET @WarningMessage = N'TestResult も削除しました。';

    END

    DELETE
    FROM    Student
    WHERE   StudentID = @StudentID;

END;

スクリプトを変更後に [実行] ボタンをクリックすると、ストアドプロシージャが変更されます。

現在の Student テーブルと TestResult テーブルのデータは次のようになっています。

以下のスクリプトを実行して、前回削除できなかった StudentID = 1 を削除してみましょう。

DECLARE @WarningMessage NVARCHAR(100);

EXEC uspStudentDelete 
        1, 
        @WarningMessage OUTPUT;

PRINT @WarningMessage;

[ 実行結果 ]

メッセージが表示され、StudentID = 1 が削除されました。

ストアドプロシージャの削除

ストアドプロシージャを削除するには DROP PROCEDURE を使います。

DROP PROCEDURE [ ストアドプロシージャ名 ];

それでは、次のスクリプトを実行して、 uspStudentDelete を削除してみましょう。

DROP PROCEDURE uspStudentDelete;

uspStudentDelete が削除されました。