February 24, 2025 at 1:43 am
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?
February 24, 2025 at 9:02 am
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.
February 24, 2025 at 3:13 pm
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
);
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy