SQL Server のユーザー定義関数を変更・削除する

前回「 SQL Server のユーザー定義のテーブル値関数を作成する」では、テーブルを返す、ユーザー定義のテーブル値関数を作ってみました。

今回は、既存のユーザー定義の関数を変更・削除してみましょう。

「 SQL Server のユーザー定義のテーブル値関数を作成する」で生成したユーザー定義関数が存在している前提で進めます。

ユーザー定義関数を変更する

ユーザー定義関数を変更するには ALTER FUNCTION を使います。

ALTER FUNCTION [ ファンクション名 ] (
    [ @入力パラメーター名 1 ] [ 入力パラメーター名 1 のデータ型 ],
    [ @入力パラメーター名 2 ] [ 入力パラメーター名 2 のデータ型 ],
    [ @入力パラメーター名 3 ] [ 入力パラメーター名 3 のデータ型 ],
    ...
)  
RETURNS [ @戻り値のテーブル名 ] TABLE (
    [カラム名 1] [カラム名 1 のデータ型] [NULL or NOT NULLなどのテーブル定義],
    [カラム名 2] [カラム名 2 のデータ型] [NULL or NOT NULLなどのテーブル定義],
    [カラム名 3] [カラム名 3 のデータ型] [NULL or NOT NULLなどのテーブル定義],
    ...
)
AS
BEGIN

    [ ファンクションで処理したい一連の T-SQL ステートメント
      @戻り値のテーブルに値をインサートする ]

    RETURN;

END;

こちらは、テーブルを返すユーザー定義関数の例ですが、CREATE の代わりに ALTER というキーワードを使う以外は CREATE の時と同じです。

詳しくはそれぞれの作成のページをご確認ください。
「 SQL Server のユーザー定義のスカラー値関数を作成する」
「 SQL Server のユーザー定義のテーブル値関数を作成する」

スクリプトを別でソース管理しているのでなければ、既存のユーザー定義関数を変更する時は、データベースに保存されているものを元に変更することも多いかと思います。

生成したユーザー定義関数は、SQL Server Management Studio のプログラミング > 関数　> テーブル値関数・スカラー値関数の箇所で見つけることができます。

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

例えば、ufnStudentTestReportGet では、TestName に TestNameEn の値を返していましたが、TestNameJp も返すように変更してみましょう。

ALTER FUNCTION [dbo].[ufnStudentTestReportGet] (
    @StudentID INT
)  
RETURNS @StudentTestReport TABLE (
    TestID          INT NOT NULL,
    TestNameEn      NVARCHAR(50) NOT NULL,
    TestNameJp      NVARCHAR(50) NOT NULL,
    Score           INT NULL,
    TestAvgScore    DECIMAL(5,2) NULL
)
AS
BEGIN
    
    INSERT INTO @StudentTestReport (
                TestID,
                TestNameEn,
                TestNameJp,
                Score
        )
        SELECT  T.TestID,
                T.TestNameEn,
                T.TestNameJp,
                TR.Score
        FROM    TestResult AS TR
                    INNER JOIN Test AS T
                        ON TR.TestID = T.TestID
        WHERE   TR.StudentID = @StudentID;


    UPDATE  T
    SET     TestAvgScore = A.TestAvgScore
    FROM    @StudentTestReport AS T
                INNER JOIN 
                    (SELECT     TestID,
                                AVG(CAST(Score AS DECIMAL)) AS TestAvgScore
                     FROM       TestResult
                     WHERE      Score IS NOT NULL
                     GROUP BY   TestID) AS A
                ON T.TestID = A.TestID;

    RETURN;

END

スクリプトを変更後に [実行] ボタンをクリックすると、ユーザー定義関数が変更されます。

以下のスクリプトを実行して、TestNameJp も返ってくるか確認してみましょう。

SELECT  *
FROM    dbo.ufnStudentTestReportGet(1);

[ 実行結果 ]

ufnStudentTestReportGet が変更されたことが確認できました。

ユーザー定義関数を削除する

ユーザー定義関数を削除するには DROP FUNCTION を使います。

DROP FUNCTION [ ファンクション名 ];

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

DROP FUNCTION ufnStudentTestReportGet;

ufnStudentTestReportGet が削除されました。