共用方式為


資料表值使用者定義函數

傳回 table 的使用者自訂函數可以作為檢視的替代方式,且功能強大。這些函數即是指資料表值函數。只要 Transact-SQL 查詢中允許使用資料表或檢視運算式的位置,都可以使用資料表值使用者自訂函數。檢視只限於單一 SELECT 陳述式,而使用者自訂函數可以包含其他陳述式,所允許的邏輯比在檢視中的還強。

資料表值使用者自訂函數也可以取代傳回單一結果集的預存程序。Transact-SQL 陳述式的 FROM 子句中可以參考使用者自訂函數所傳回的資料表,但不能參考傳回結果集的預存程序。

資料表值使用者自訂函數的元件

在資料表值使用者自訂函數中:

  • RETURNS 子句定義函數傳回的資料表之區域傳回變數名稱。RETURNS 子句也定義資料表的格式。區域傳回變數名稱的範圍,在函數中是屬於區域性的。

  • 函數主體中的 Transact-SQL 陳述式會在 RETURNS 子句定義的傳回變數中建立與插入資料列。

  • 當執行 RETURN 陳述式時,插入變數中的資料列會以函數的表格化輸出型式傳回。RETURN 陳述式中不得使用引數。

在資料表值函數中,沒有 Transact-SQL 陳述式可以直接將結果集傳回給使用者。函數所傳回的 table,就是它唯一能傳回給使用者的資訊。

[!附註]

由使用者自訂函數傳回的資料表,其 text in row 資料表選項會自動設為 256。這項設定無法變更。READTEXT、WRITETEXT 及 UPDATETEXT 陳述式皆不得用來讀取或寫入資料表中 text、ntext 或 image 資料行的任一部份。如需詳細資訊,請參閱<同資料列資料>。

範例

下列範例會建立 dbo.ufnGetContactInformation 函數,並示範資料表值函數的元件。在此函數中,傳回的本機變數名稱是 @retContactInformation。在函數本體中的陳述式,會將資料列插入此變數,以便建立由函數傳回的資料表結果。

USE AdventureWorks2008R2;
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 = BusinessEntityID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Person 
    WHERE BusinessEntityID = @ContactID;
    -- Get contact job title
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
                THEN (SELECT JobTitle 
                      FROM HumanResources.Employee AS e
                      WHERE e.BusinessEntityID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)
            ELSE NULL 
        END;
    -- Get contact type
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
            THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
            THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
            THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN') 
            THEN 'Consumer'
             -- Check for general contact
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC') 
            THEN 'General Contact'
        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

下列範例會在 SELECT 陳述式的 FROM 子句中使用資料表值函數 dbo.ufnGetContactInformation。

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