Convert stored procedure into a Table-valued function

  • Feel like I'm missing something blatantly obvious.

    I am trying to return the cost of each of a set of ingredients as of a specific date (so I can sum them in the next step, for example). It works fine if I create it as a stored procedure, but when I try to create a function that returns a table, it throws an error. Here's the code that I wanted to return a table:

    CREATE FUNCTION LoafIngredientCostsOverTime
    (@StartDate DATE = '2022-01-01')
    RETURNS TABLE
    AS
    /*
    I'm trying to get the unit cost for each Product for each week over a span of time,
    whether there are purchases or not.
    */

    /*
    Get a list of dates...
    */

    WITH Dates(TheDate)
    AS
    (
    SELECT DATEADD(week, x.WeekNo, @StartDate)
    FROM
    (SELECT TOP 80 ROW_NUMBER() OVER (ORDER BY ao.name) - 1 AS WeekNo
    FROM sys.all_objects ao) x
    ),
    Products(RecipeID,RecipeName, Yield, SalePrice)
    AS
    (
    SELECT RecipeID, [Recipe Name], Yield, SalePrice
    FROM synProduct
    )
    SELECTd.TheDate
    ,p.RecipeID
    ,p.RecipeName
    ,p.SalePrice
    ,p.Yield
    ,br.IngredientID
    ,br.IngredientName
    ,br.RecipeWeight
    ,i.IngredientWeight
    --,pctOfPkg = br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    --,RecipeIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    ,PerLoafIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)/p.Yield
    ,lp.PurchaseDate
    --,COALESCE(lp.UnitPrice,i.UnitPrice)
    FROM Dates d
    CROSS JOIN Products p
    INNER JOIN vwBaseIngredientRecipe br
    ON p.RecipeID = br.RecipeID
    INNER JOIN Ingredient i
    ON i.IngredientID = br.IngredientID
    /* for each (IngredientID, PurchaseDate) combination, return the latest unit price */
    OUTER APPLY (
    SELECT TOP 1 pu.IngredientID, pu.PurchaseDate, pu.UnitPrice
    FROM Purchases pu
    WHERE pu.IngredientID = br.IngredientID
    AND pu.PurchaseDate < d.TheDate
    ORDER BY pu.PurchaseDate DESC
    ) lp


    if I tweak it so that it's back to being a stored procedure, it works fine:

    CREATE PROCEDURE  usp_LoafIngredientCostsOverTime
    @StartDate DATE = '2022-01-01'
    AS
    /*
    I'm trying to get the unit cost for each Product for each week over a span of time,
    whether there are purchases or not.
    */

    /*
    Get a list of dates...
    */

    WITH Dates(TheDate)
    AS
    (
    SELECT DATEADD(week, x.WeekNo, @StartDate)
    FROM
    (SELECT TOP 80 ROW_NUMBER() OVER (ORDER BY ao.name) - 1 AS WeekNo
    FROM sys.all_objects ao) x
    ),
    Products(RecipeID,RecipeName, Yield, SalePrice)
    AS
    (
    SELECT RecipeID, [Recipe Name], Yield, SalePrice
    FROM synProduct
    )
    SELECTd.TheDate
    ,p.RecipeID
    ,p.RecipeName
    ,p.SalePrice
    ,p.Yield
    ,br.IngredientID
    ,br.IngredientName
    ,br.RecipeWeight
    ,i.IngredientWeight
    --,pctOfPkg = br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    --,RecipeIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    ,PerLoafIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)/p.Yield
    ,lp.PurchaseDate
    --,COALESCE(lp.UnitPrice,i.UnitPrice)
    FROM Dates d
    CROSS JOIN Products p
    INNER JOIN vwBaseIngredientRecipe br
    ON p.RecipeID = br.RecipeID
    INNER JOIN Ingredient i
    ON i.IngredientID = br.IngredientID
    /* for each (IngredientID, PurchaseDate) combination, return the latest unit price */
    OUTER APPLY (
    SELECT TOP 1 pu.IngredientID, pu.PurchaseDate, pu.UnitPrice
    FROM Purchases pu
    WHERE pu.IngredientID = br.IngredientID
    AND pu.PurchaseDate < d.TheDate
    ORDER BY pu.PurchaseDate DESC
    ) lp

    I wanted to be able to join the result to other tables (maybe purchases across time), but to do that, I'd need it to return a regular table. So how do I do that?

     

  • You don't mention the error you are getting, but I think you might have missed a bit of syntax:

    CREATE FUNCTION LoafIngredientCostsOverTime
    (
    @StartDate DATE = '2022-01-01'
    )
    RETURNS TABLE
    AS
    RETURN
    (
    -- Your query here
    );

    I'd suggest you consider adding 'WITH SCHEMABINDING' and using universal ISO format for your literal dates (YYYYMMDD).

    You'll also have to remove your ORDER BY clause, as that's not permitted in TVFs.

    • This reply was modified 3 months, 2 weeks ago by Phil Parkin.
    • This reply was modified 3 months, 2 weeks ago by Phil Parkin.

  • Oh, like thaaaaaaat! Thanks!  Proof that I almost never write table-valued functions... mostly just lots of Common Table Expressions. Come to think of it, is there a good article that compares the performance of the two?

    I figured it was something stupid. Here's the fixed code.

    CREATE FUNCTION LoafIngredientCostsOverTime
    (
    @StartDate DATE
    )
    RETURNS TABLE
    AS
    RETURN
    (
    -- Your query here

    WITH Dates(TheDate)
    AS
    (
    SELECT DATEADD(week, x.WeekNo, @StartDate)
    FROM
    (SELECT TOP 80 ROW_NUMBER() OVER (ORDER BY ao.name) - 1 AS WeekNo
    FROM sys.all_objects ao) x
    ),
    Products(RecipeID,RecipeName, Yield, SalePrice)
    AS
    (
    SELECT RecipeID, [Recipe Name], Yield, SalePrice
    FROM synProduct
    )
    SELECTd.TheDate
    ,p.RecipeID
    ,p.RecipeName
    ,p.SalePrice
    ,p.Yield
    ,br.IngredientID
    ,br.IngredientName
    ,br.RecipeWeight
    ,i.IngredientWeight
    --,pctOfPkg = br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    --,RecipeIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)
    ,PerLoafIngredientCost = COALESCE(lp.UnitPrice,i.UnitPrice) * br.RecipeWeight/(IIF(i.IngredientWeight = 0, 1, i.IngredientWeight) * 1000)/p.Yield
    ,lp.PurchaseDate
    --,COALESCE(lp.UnitPrice,i.UnitPrice)
    FROM Dates d
    CROSS JOIN Products p
    INNER JOIN vwBaseIngredientRecipe br
    ON p.RecipeID = br.RecipeID
    INNER JOIN Ingredient i
    ON i.IngredientID = br.IngredientID
    /* for each (IngredientID, PurchaseDate) combination, return the latest unit price */OUTER APPLY (
    SELECT TOP 1 pu.IngredientID, pu.PurchaseDate, pu.UnitPrice
    FROM Purchases pu
    WHERE pu.IngredientID = br.IngredientID
    AND pu.PurchaseDate < d.TheDate
    ORDER BY pu.PurchaseDate DESC
    ) lp

    );

    • This reply was modified 3 months, 2 weeks ago by pietlinden.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply