テーブル型を戻り値とする関数の作成方法

SQLServerでは「ユーザー定義型」と呼ばれる独自のテーブル型をした戻り値を返す関数を作成することができます。

その作り方と使い方を解説します。

SQLServerでテーブル型を戻り値とする関数を作る

SQLServerではINT型やVARCHAR型などを返す関数と同様にユーザー定義テーブル型を戻り値とする関数を作成できます。

テーブル型を戻り値とする関数の作成

テーブル型を戻り値とする関数は通常の関数と同様にCREATE FUNCTIONで作成します。

CREATE  FUNCTION [dbo].[test_function]
(
     @param1    VARCHAR(MAX) ='' 
    ,@param2    VARCHAR(MAX) ='' 
)  
RETURNS @retTbl TABLE
(
     no   INT
    ,name VARCHAR(20)
)
BEGIN 

    INSERT INTO @retTbl VALUES( 1, '太郎' ) ;
    INSERT INTO @retTbl VALUES( 2, '次郎' ) ;
    INSERT INTO @retTbl VALUES( 3, '三郎' ) ;

    RETURN
END

通常の関数との違いは「RETURNS」に続く部分が

RETURNS @retTbl TABLE
(
     no   INT
    ,name VARCHAR(20)
)

と戻り値が呼び出し元に返したい形の
ユーザー定義テーブル型になっています。

戻り値のテーブルに値をINSERTして返します。
INSERTしない状態で関数を終了すると

     SELECT 1 AS no , '太郎' AS name

メッセージ 444、レベル 16、状態 3、プロシージャ test_function、行 13
関数内に含まれる SELECT ステートメントは、クライアントにデータを返せません。

となり、エラーで関数が作成できません。

また、最後は「RETURN」で終了している必要があります。

呼び出しかた

作成した関数を呼び出すには

SELECT * FROM  dbo.test_function( 'param1','param2' )

のようにFROM句に指定して呼び出します。

テーブルと同じ扱いです。

実行結果は

no    name
 1    太郎
 2    次郎
 3    三郎

です。

また、他のテーブルの値をSELECTして挿入することも可能です。

CREATE  FUNCTION [dbo].[test_function]
(
     @param1    VARCHAR(MAX) ='' 
    ,@param2    VARCHAR(MAX) ='' 
)  
RETURNS @retTbl TABLE
(
     no   INT
    ,name VARCHAR(20)
)
BEGIN 
    INSERT INTO @retTbl SELECT no, name FROM other_table
    RETURN
END

本来1つしか戻り値を返せない関数もこの方法を利用すれば複数行の検索結果を返す関数にすることができます。

VIEWを作成するより色々なロジックを組めるので複雑な処理が可能になります。