【SQL】內嵌資料表值函數（ITVF）

內嵌資料表值函數像是帶有參數的 VIEW

use AdventureWorks2012

IF OBJECT_ID(N’dbo.CustomerOrder’) IS NOT NULL
DROP FUNCTION dbo.CustomerOrder
GO

CREATE FUNCTION dbo.CustomerOrder(@customerid int,@year int)
RETURNS TABLE
AS
RETURN
(
SELECT
SOH.CustomerID AS 客戶編號,
YEAR(SOH.OrderDate) AS 年份,
SUM(ROUND(SOD.OrderQty * SOD.UnitPrice * (1 – SOD.UnitPriceDiscount),0)) AS 總金額
FROM [Sales].[SalesOrderHeader] AS SOH
JOIN [Sales].[SalesOrderDetail] AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE SOH.CustomerID = @customerid
AND YEAR(OrderDate) = @year
GROUP BY SOH.CustomerID,YEAR(OrderDate)
)
GO

SELECT * FROM dbo.CustomerOrder(29825,2011)
GO

最後可使用建立的FUNCTION，查對客戶代號與年份，呈現結果如上圖。