APPLYを使用して、行ごとにテーブル値関数を呼び出します.

 



USE AdventureWorks





-----   CROSS APPLY ------



-----      -----

CREATE FUNCTION dbo.fn_WorkOrderRouting

(@WorkOrderID int) RETURNS TABLE

AS

RETURN

SELECT WorkOrderID,

ProductID,

OperationSequence,

LocationID

FROM Production.WorkOrderRouting

WHERE WorkOrderID = @WorkOrderID

GO



----- Production.WorkOrder   WorkOrderID      



SELECT w.WorkOrderID,

w.OrderQty,

r.ProductID,

r.OperationSequence

FROM Production.WorkOrder w

CROSS APPLY dbo.fn_WorkOrderRouting

(w.WorkOrderID) AS r

ORDER BY w.WorkOrderID,

w.OrderQty,

r.ProductID





---   OUTER APPLY



INSERT INTO [AdventureWorks].[Production].[WorkOrder]

(ProductID,

OrderQty,

ScrappedQty,

StartDate,

EndDate,

DueDate,

ScrapReasonID,

ModifiedDate

)

VALUES

(1,

1,

1,

GETDATE(),

GETDATE(),

GETDATE(),

1,

GETDATE()

)





SELECT w.WorkOrderID,

w.OrderQty,

r.ProductID,

r.OperationSequence

FROM Production.WorkOrder AS w

CROSS APPLY dbo.fn_WorkOrderRouting

(w.WorkOrderID) AS r

WHERE w.WorkOrderID IN

(SELECT MAX(WorkOrderID)

FROM Production.WorkOrder)