表值函數

    SQL Server中提供了類似其他編程語言的函數，而函數的本質通常是一段代碼的封裝，并返回值。在SQL Server中，函數除了可以返回簡單的數據類型之外（Int、Varchar等），還可以返回一個集合，也就是返回一個表。
    而根據是否直接返回集合或是定義后再返回集合，表值函數又分為內聯用戶定義表值函數和用戶定義表值函數（下文統稱為表值函數，省去“用戶定義”四個字）。

內聯表值函數
    內聯表值函數和普通函數并無不同，唯一的區別是返回結果為集合（表），而不是簡單數據類型，一個簡單的內聯表值函數如代碼清單1所示（摘自VEVB）。

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion( @Region nvarchar(50) )RETURNS tableASRETURN (SELECT DISTINCT s.Name AS Store, a.CityFROM Sales.Store AS sINNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressIDINNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceIDWHERE sp.Name = @Region);GO

代碼清單1.一個簡單的表值函數

用戶定義表值函數
而用戶定義表值函數，需要在函數開始時定義返回的表結構，然后可以寫任何代碼進行數據操作，插入到定義的表結構之后進行返回，一個稍微負責的用戶定義表值函數示例如代碼清單2所示（摘自VEVB）。

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