省略可能な引数を持つ関数の作成 [SQLServer]

SQLServerではユーザーが定義する独自の関数をデータベース内に作成することができます。
ユーザー定義関数は、値を返すスカラー関数やテーブルのような結果表を返すテーブル値関数が作成でき、関数には結果を返すのに必要な引数を指定することができます。
関数を作っていると、引数が必要な場合と特定の場合は引数を指定しなくてもよい場合が出てきます。

例えば簡単な例では、数値型の引数を3つ取る関数があり、3つの引数の合計値を算出するとします。
第1引数と第2引数は必須で戻り値として第1引数と第2引数を加算した結果を返します。
第3引数は指定があれば上記の結果にさらに第3引数の値を加算した値を戻り値として返し、第3引数の指定がなければ上記の結果（第1引数と第2引数のみを加算した値）を戻り値として返す関数です。

今回は上記のような関数を作成する際に必要になる
「SQLServerにユーザー定義関数を作成する際の引数を省略可能にするための記述方法」
について紹介します。

1 省略可能な引数を定義した関数の作成例
- 1.1 サンプル関数のSQL
2 省略可能な引数を定義した関数の使用例
- 2.1 引数を省略しない場合
- 2.2 引数を省略する場合

省略可能な引数を定義した関数の作成例

省略可能な引数を持つ関数の作成例として、年齢を取得する関数を作成します。
この関数は引数に2つの日付型の値を取ります。
第1引数には生年月日を取り、第2引数には年齢を算出する基準となる日付を取ります。
第2引数の基準日は指定がある場合は指定された日付時点の年齢を算出し、指定がない場合は現在の日付（GETDATE関数で取得できるサーバー日付）時点の年齢を算出します。

サンプル関数のSQL

省略可能な引数を持つ年齢を算出する関数のサンプルSQLを次に示します。
この関数は「@DATE_OF_BIRTH AS DATE（生年月日）」と「@REFERENCE_DATE AS DATE（基準日）」の2つの引数を取ります。2つの引数のうち「@REFERENCE_DATE AS DATE（基準日）」が省略可能な引数になります。

/*
 * 年齢を取得する関数
 * 
 * 引数
 *      @DATE_OF_BIRTH  生年月日 AS DATE  
 *      @REFERENCE_DATE 基準日   AS DATE（指定されない場合はGETDATE関数で取得する現在の日付）
 * 戻り値
 *      年齢 AS INT
 */
CREATE FUNCTION GET_AGE
(
    @DATE_OF_BIRTH AS DATE,
    @REFERENCE_DATE AS DATE = NULL
)
RETURNS INT
AS
BEGIN
    -- 基準日を取得
    -- 引数に指定されていない（NULLの場合）はGETDATE関数で取得した日付を基準日にする
    SET @REFERENCE_DATE = CASE WHEN @REFERENCE_DATE IS NULL THEN GETDATE() ELSE @REFERENCE_DATE END

    -- 年の差を取得
    DECLARE @DIFFERENCE INT = (SELECT DATEDIFF(YEAR, @DATE_OF_BIRTH, @REFERENCE_DATE));

    -- 生年月日の月日
    DECLARE @MONTHDAY_OF_BIRTH VARCHAR(4) = RIGHT(CONVERT(VARCHAR(8), @REFERENCE_DATE, 112), 4);
    -- 基準日の月日
    DECLARE @REFERENCE_MONTHDAY VARCHAR(4) = RIGHT(CONVERT(VARCHAR(8), @DATE_OF_BIRTH, 112), 4);

    -- 年齢再設定
    DECLARE @AGE INT = CASE
        WHEN @MONTHDAY_OF_BIRTH < @REFERENCE_MONTHDAY THEN
            -- 生年月日の月日が基準日の月日より前の場合は年の差から1を差し引く
            @DIFFERENCE - 1
        ELSE 
            @DIFFERENCE
        END;

    -- 年齢を返す
    RETURN @AGE;
END

* 年齢を取得する関数

* 引数

* @DATE_OF_BIRTH 生年月日 AS DATE

* @REFERENCE_DATE 基準日 AS DATE（指定されない場合はGETDATE関数で取得する現在の日付）

* 戻り値

* 年齢 AS INT

CREATE FUNCTION GET_AGE

(

@DATE_OF_BIRTH AS DATE,

@REFERENCE_DATE AS DATE = NULL

)

RETURNS INT

BEGIN

-- 基準日を取得

-- 引数に指定されていない（NULLの場合）はGETDATE関数で取得した日付を基準日にする

SET @REFERENCE_DATE = CASE WHEN @REFERENCE_DATE IS NULL THEN GETDATE() ELSE @REFERENCE_DATE END

-- 年の差を取得

DECLARE @DIFFERENCE INT = (SELECT DATEDIFF(YEAR, @DATE_OF_BIRTH, @REFERENCE_DATE));

-- 生年月日の月日

DECLARE @MONTHDAY_OF_BIRTH VARCHAR(4) = RIGHT(CONVERT(VARCHAR(8), @REFERENCE_DATE, 112), 4);

-- 基準日の月日

DECLARE @REFERENCE_MONTHDAY VARCHAR(4) = RIGHT(CONVERT(VARCHAR(8), @DATE_OF_BIRTH, 112), 4);

-- 年齢再設定

DECLARE @AGE INT = CASE

WHEN @MONTHDAY_OF_BIRTH < @REFERENCE_MONTHDAY THEN

-- 生年月日の月日が基準日の月日より前の場合は年の差から1を差し引く

@DIFFERENCE - 1

ELSE

@DIFFERENCE

END;

-- 年齢を返す

RETURN @AGE;

END

省略可能な引数を定義した関数の使用例

作成した年齢を取得する関数を使う側のSQLについて記載します。
ここでは、関数を呼び出す際に引数を省略しない場合と、引数を省略する場合の2つのパターンでのSQLをサンプルとして示します。

引数を省略しない場合

引数を省略しない場合の関数の使用例です。

-- 引数を省略しない場合
SELECT dbo.GET_AGE('2010/12/11', '2018/12/10') AS AGE;

1 2	-- 引数を省略しない場合 SELECT dbo.GET_AGE('2010/12/11', '2018/12/10') AS AGE;

実行結果

省略可能な引数がない関数を呼び出す場合と同じなので問題はないと思います。

引数を省略する場合

引数を省略する場合の関数の使用例です。

-- 引数を省略する場合
SELECT dbo.GET_AGE('2010/12/10', DEFAULT) AS AGE;

1 2	-- 引数を省略する場合 SELECT dbo.GET_AGE('2010/12/10', DEFAULT) AS AGE;

実行結果

省略可能な引数がある場合は記述を省略するのではなく、「DEFAULT」と記述します。
SQLServerの省略可能な引数を持つ関数では、完全に引数を省略することはできません。
（ストアドプロシージャの場合は省略可能な引数を持つ場合に完全に省略ができますが関数ではできません。）

省略可能な引数を持つストアドプロシージャについては、以下の記事を参考にしてください。