Multi-statement Scalar to Multi-statement TVF

Question

I have a question on these few lines of code, particularly how this @workTable is being populated with the StartingCost and EndingCost values:

DECLARE @workTable TABLE
                (
                  ProductId INT ,
                  StartingCost MONEY ,
                  EndingCost MONEY
                ) ;

The full query is listed below.

IF OBJECT_ID(N'Production.ms_tvf_ProductCostDifference',N'TF' ) IS NOT NULL

--SELECT * FROM sys.objects WHERE name LIKE 'm%'
    DROP FUNCTION Production.ms_tvf_ProductCostDifference ;
GO

CREATE FUNCTION Production.ms_tvf_ProductCostDifference
    (
      @StartDate DATETIME ,
      @EndDate DATETIME 
    )
RETURNS @retCostDifference TABLE
    (
      ProductId INT ,
      CostDifference MONEY
    )
AS
    BEGIN
        DECLARE @workTable TABLE
            (
              ProductId INT ,
              StartingCost MONEY ,
              EndingCost MONEY
            ) ;

        INSERT  INTO @retCostDifference
                ( ProductId ,
                  CostDifference
                )
                SELECT  ProductID ,
                        StandardCost
                        FROM    ( SELECT    pch.ProductID ,
                                    pch.StandardCost ,
                                    ROW_NUMBER() OVER
                                     ( PARTITION BY ProductID
                                       ORDER BY StartDate DESC ) AS rn
                          FROM      Production.ProductCostHistory AS pch
                          WHERE     EndDate BETWEEN
                                                 @StartDate AND @EndDate
                        ) AS x
                WHERE   x.rn = 1 ;

        UPDATE  @retCostDifference
        SET     CostDifference = CostDifference - StandardCost
        FROM    @retCostDifference cd
                JOIN ( SELECT   ProductID ,
                                StandardCost
                       FROM     ( SELECT    pch.ProductID ,
                                            pch.StandardCost ,
                                            ROW_NUMBER() OVER
                                             ( PARTITION BY ProductID
                                               ORDER BY StartDate ASC )
                                               AS rn
                                  FROM      Production.ProductCostHistory 
                                                AS pch
                                  WHERE     EndDate BETWEEN
                                                 @StartDate AND @EndDate
                                ) AS x 
                       WHERE    x.rn = 1
                     ) AS y ON cd.ProductId = y.ProductID ;

        RETURN ; select top 20 * from Production.ProductCostHistory 
          END
Go

/******************************************************************************** The code above represents

Listing 17: A multi-statement TVF

This TVF, Instead of retrieving a single row from the database and calculating the price difference, pulls back all rows from the database and calculates the price difference for all rows at once. *********************************************************************************/

SELECT  p.ProductID ,
        p.Name ,
        p.ProductNumber ,
        pcd.CostDifference
FROM    Production.Product AS p
        INNER JOIN Production.ms_tvf_ProductCostDifference
                               ('2001-01-01', GETDATE()) AS pcd
                                 ON p.ProductID = pcd.ProductID ;

score 0 · Answer 1 · answered Jul 17 '16 at 04:35

0

In your code, the below block is simply declared and it never used.

DECLARE @workTable TABLE
                (
                  ProductId INT ,
                  StartingCost MONEY ,
                  EndingCost MONEY
                ) ;

Since @retCostDifference is the return table for this function and all the transactions (INSERT, UPDATE) are happened to the @retCostDifference table only.

There is no use of @workTable in the function.

answered Jul 17 '16 at 04:35

Arulkumar

12,966
14
47
68

This is what I think. But then the question is how does @retCostDifference find the CostDifference when product with the same ID is sold 3 times each time at a different price? Without knowing StartingCost and EndingCost this is not possible taking into account that Insert INTO ... SELECT (sorting by StartDate DESC) returns the starting price and the the UPDATE (sorting by StartDate ASC) does not always return greatest ending Price (in case the ending price is different but the date the product sold is the same; say we do not track the time product is sold, just date). – Data Engineer Jul 18 '16 at 03:33
So seems like there should be a need for the @workTable, shouldn't it? – Data Engineer Jul 18 '16 at 03:35
Just as a tip I'm using MS AdventureWorks db. 2008 and 2012 versions are fine to test on. – Data Engineer Jul 18 '16 at 03:37

Multi-statement Scalar to Multi-statement TVF

Listing 17: A multi-statement TVF

1 Answers1