星期二, 4月 19, 2022

[SQL] 可更新的 Function

同事分享時提到 iTVF 可以更新資料表資料,MSTVF 不行,當場愣了一下,腦海裡對於 Function 使用,都是應用在 select 相關,壓根沒有想要透過 function 來更新資料,在 User-Defined Functions 內 Valid statements in a function 章節有提到 
UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function. 
利用 AdventureWorks2019 來驗證

建立 iTVF
use AdventureWorks2019
go

CREATE FUNCTION udfGetSalesOrderHeader(@SalesOrderID int)
RETURNS TABLE
AS
	RETURN
	(
		SELECT 
			SOH.SalesOrderID ,
			SOH.OrderDate ,
			SOH.ShipDate ,
			SOH.SalesOrderNumber ,
			SOH.SubTotal ,
			SOH.CustomerID ,
			P.LastName , 
			P.MiddleName , 
			P.FirstName
		FROM [Sales].[SalesOrderHeader] AS SOH
			JOIN [Sales].[Customer] AS C ON SOH.CustomerID = C.CustomerID
			LEFT JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID
		WHERE SOH.SalesOrderID = @SalesOrderID
	) 
GO
實際測試
-- 資料原日期為 2011-08-08 
SELECT * FROM udfGetSalesOrderHeader(44132)

UPDATE udfGetSalesOrderHeader(44132)
SET ShipDate = DateFromParts(2022,4,18)

-- 更新後日期為 2022-04-18
SELECT * FROM udfGetSalesOrderHeader(44132)

沒有留言:

張貼留言