函數類型
SQL Server 2008 支援使用者定義函數和內建系統函數。
純量函數
使用者定義純量函數會傳回在 RETURNS 子句中所定義類型的單一資料值。內嵌純量函數並沒有函數主體；純量值為單一陳述式的結果。多重陳述式純量函數的函數主體是在 BEGIN...END 區塊中所定義，包含傳回單一值的 Transact-SQL 陳述式系列。傳回類型可以是任何資料類型，但 text、ntext、image、cursor 和 timestamp 除外。
下例會建立多重陳述式純量函數。這個函數使用了一個輸入值 ProductID，並傳回單一資料值，也就是指定產品的彙總存貨量。
USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
下例使用了 ufnGetInventoryStock 函數，傳回 ProductModelID 介於 75 和 80 之間的產品目前的存貨量。
USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
資料表值函式
使用者定義資料表值函式會傳回 table 資料類型。內嵌資料表值函式並沒有函數主體；資料表為單一 SELECT 陳述式的結果集。
下例會建立一個內嵌資料表值函式。這個函數使用了一個輸入參數，也就是客戶 (商店) 識別碼，並傳回 ProductID 和 Name 資料行，以及從年初至今將每項產品銷售給商店的彙總銷售額 YTD Total。
USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
下例會叫用這個函數並指定客戶識別碼 602。
SELECT * FROM Sales.ufn_SalesByStore (602);
多重陳述式資料表值函式的函數主體是在 BEGIN...END 區塊中所定義，其包含的 Transact-SQL 陳述式會建立及插入資料列到傳回的資料表。
下例會建立一個資料表值函式。這個函數使用了單一輸入參數 EmployeeID，並傳回一份清單，列出這位指定員工的所有直接或間接下屬。然後叫用此函數並指定員工識別碼 109。
USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
內建函數
為了協助您執行各種作業，SQL Server 提供了內建函數。這些函數不能修改。您可以在 Transact-SQL 陳述式中使用內建函數來：
從 SQL Server 系統資料表存取資訊，而不用直接存取系統資料表。如需詳細資訊，請參閱＜使用系統函數＞。
執行如 SUM、GETDATE 或 IDENTITY 等常見工作。如需詳細資訊，請參閱＜內建函數 (Transact-SQL)＞。
內建函數會傳回純量或 table 資料類型。例如，如果最後一個 Transact-SQL 陳述式執行成功，@@ERROR 會傳回 0。如果陳述式產生錯誤，則 @@ERROR 會傳回錯誤號碼。而函數 SUM(parameter) 則會傳回該參數所有值的總和。