はじめに
ビューやストアドプロシージャを使用すれば、Transact-SQL（T-SQL）コードを適切にモジュール化したり、切り離したりすることができます。しかし、それ以上のことを望んだことはありませんか？ 例えば、SELECT文の中でパラメータドリブンのビューやストアドプロシージャを使えたらいいのに、と思ったことはないでしょうか。SQL Serverには、あまり目立たないながら検討に値するビューとストアドプロシージャの代替手段があります。その代替手段とは、テーブル値ユーザー定義関数（UDF）です。テーブル値UDFは、ビューとストアドプロシージャの重要な機能をすべて備えているだけでなく、ビューとストアドプロシージャにはない別の機能も備えています。
例えば、私の開発チームでは、テーブル値UDFを使って、旧式のASPビジネスインテリジェンスアプリケーションに、新しいフィルタリングのレイヤを追加しました。SELECT文のFROM句内のテーブルの代わりにパラメータドリブンUDFを使用するだけで、核のT-SQLの大部分は変更せずに済みました。さらに、「検索と置換」を使用することで、ほとんどの変更を自動化することもできました。
本稿では、SQL Server 2005に付属している「AdventureWorks」サンプルデータベースを修正したサンプルを使って、T-SQLコード内でテーブル値UDFを効果的に使用する方法について説明します。「AdventureWorks」データベースに格納されているUDFを確認するには、Management Studioを使って、図1に示すデータベース内の領域にナビゲートします。
すべてのUDFは、ストアドプロシージャやビューと同様にT-SQLで記述され、ストアドプロシージャと同様にパラメータ値を持ちます。UDFを定義するには、CREATE FUNCTION文を使用します。
ユーザー定義関数の定義
ユーザー定義関数には、次の2種類があります。
- スカラ値UDF
- テーブル値UDF
スカラUDFは、DML文（INSERT、UPDATE、SELECTなど）またはT-SQL文の内部で使用できます。テーブル値UDFにはいくつかの制限があり、一般にはSELECT文のFROM句で使用します。本稿では、テーブル値UDFの使用に焦点を当てます。
テーブル値UDFの概要
テーブル値UDFには、次の2種類があります。
- インラインテーブル値関数
- 複数ステートメントテーブル値関数
TABLEデータ型を返します。各関数は、1つのT-SQL文で構成されます。どちらのテーブル値UDFも、単一の結果セットを返します。インラインテーブル値UDFの定義の例を以下に示します。
CREATE FUNCTION dbo.TestInlineFunctionName ( ) RETURNS TABLE AS RETURN ( SELECT 0 as RetVal,* from [Person].[Contact] )
複数ステートメントUDFはインラインUDFに似ていますが、大きな違いが1つあります。それは、次のサンプルコードに示すように、RETURNSディレクティブの後にテーブル定義が続くことです。
RETURNS @retContactInformation TABLE ( -- Columns returned by the function [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL )
複数ステートメントUDFには複数のSELECT文（他のT-SQL文も同様）を指定できるため、次のサンプルコードのようにして、返すテーブルに明示的にデータを割り当てる必要があります。
INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
複数ステートメントテーブル値UDFを使用すると、返されるテーブルの内容を変更するといった処理を行うことができます。例えば、次のコードは、複数ステートメントUDFの中で完全に有効です。
UPDATE @retContactInformation SET [JobTitle] = 'None'
テーブル値関数を理解できたところで、これをビューとストアドプロシージャに置き換えて使用する方法について説明しましょう。
