資料表值使用者自訂函數
傳回 table 的使用者自訂函數可以作為檢視的替代方式，且功能強大。這些函數即是指資料表值函數。只要 Transact-SQL 查詢中允許使用資料表或檢視運算式的位置，都可以使用資料表值使用者自訂函數。檢視只限於單一 SELECT 陳述式，而使用者自訂函數可以包含其他陳述式，所允許的邏輯比在檢視中的還強。
資料表值使用者自訂函數也可以取代傳回單一結果集的預存程序。Transact-SQL 陳述式的 FROM 子句中可以參考使用者自訂函數所傳回的資料表，但不能參考傳回結果集的預存程序。
資料表值使用者自訂函數的元件
在資料表值使用者自訂函數中：
- RETURNS 子句定義函數傳回的資料表之區域傳回變數名稱。RETURNS 子句也定義資料表的格式。區域傳回變數名稱的範圍，在函數中是屬於區域性的。
- 函數主體中的 Transact-SQL 陳述式會在 RETURNS 子句定義的傳回變數中建立與插入資料列。
- 當執行 RETURN 陳述式時，插入變數中的資料列會以函數的表格化輸出型式傳回。RETURN 陳述式中不得使用引數。
在資料表值函數中，沒有 Transact-SQL 陳述式可以直接將結果集傳回給使用者。函數所傳回的 table，就是它唯一能傳回給使用者的資訊。
.gif "ms191165.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 由使用者自訂函數傳回的資料表，其 text in row 資料表選項會自動設為 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
下列範例會在 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
請參閱
概念
內嵌使用者自訂函數
決定性與非決定性函數
將預存程序重寫為函數