次の方法で共有


ユーザー定義テーブル値関数

table データ型の値を返すユーザー定義関数は、ビューに代わる強力なツールになります。このような関数をテーブル値関数と呼びます。ユーザー定義テーブル値関数は、Transact-SQL クエリ内のテーブル式またはビュー式を指定できる場所で使用できます。ビューに含めることができるのは、1 つの SELECT ステートメントだけですが、ユーザー定義関数には、ビューで使用できるロジックより高度なロジックを使用できる追加のステートメントを含めることができます。

ユーザー定義テーブル値関数を、単一の結果セットを返すストアド プロシージャの代わりに使用することもできます。ユーザー定義関数で返されるテーブルは Transact-SQL ステートメントの FROM 句から参照できますが、ストアド プロシージャから返される結果セットは参照できません。

ユーザー定義テーブル値関数の構成要素

ユーザー定義テーブル値関数は、次のように構成されます。

  • RETURNS 句でユーザー定義関数が返すテーブルのローカル戻り変数の名前を定義します。さらに、RETURNS 句では、テーブルのフォーマットも定義します。ローカル戻り変数の名前のスコープは、関数内でローカルです。
  • 関数本体の Transact-SQL ステートメントは、行を作成し、RETURNS 句によって定義された戻り変数に行を挿入します。
  • RETURN ステートメントの実行時、変数に挿入された行は、関数の表形式出力として返されます。RETURN ステートメントに、引数を指定することはできません。

テーブル値関数の Transact-SQL ステートメントは、ユーザーに直接結果セットを返すことはできません。関数がユーザーに返すことができる唯一の情報は、関数が返す table のみです。

ms191165.note(ja-jp,SQL.90).gifメモ :
text in row テーブル オプションは、ユーザー定義関数が返すテーブル 1 つに対して 256 行に自動的に設定されます。この値は変更できません。READTEXT、WRITETEXT、および UPDATETEXT ステートメントを使用して、テーブル内の text 型、ntext 型、または image 型の列の一部の読み取りや書き込みを行うことはできません。詳細については、「行内データ」を参照してください。

上記の例は、dbo.ufnGetContactInformation 関数を作成し、テーブル値関数の構成要素を示しています。この関数のローカルの戻り変数の名前は @retContactInformation です。関数本体のステートメントでは、行をこの変数に挿入して関数が返すテーブルの結果が作成されます。

USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
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
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @JobTitle nvarchar(50), 
        @ContactType nvarchar(50);
    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN (SELECT Title 
                    FROM HumanResources.Employee 
                    WHERE ContactID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Purchasing.VendorContact AS vc 
                        INNER JOIN Person.ContactType AS ct 
                        ON vc.ContactTypeID = ct.ContactTypeID 
                    WHERE vc.ContactID = @ContactID)
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Sales.StoreContact AS sc 
                        INNER JOIN Person.ContactType AS ct 
                        ON sc.ContactTypeID = ct.ContactTypeID 
                    WHERE ContactID = @ContactID)
            ELSE NULL 
        END;
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

次の例では、2 つの SELECT ステートメントの FROM 句でテーブル値関数 dbo.ufnGetContactInformation を使用しています。

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

参照

概念

インライン ユーザー定義関数
決定的関数と非決定的関数
関数としてのストアド プロシージャの書き直し

その他の技術情報

ユーザー定義関数の設計

ヘルプおよび情報

SQL Server 2005 の参考資料の入手