SQL Function within Function

Question

I have a function that gets the recipeid. I am creating another function that shows the average price with recipename. How do I use the first function within the second function?

This is what I have so far. As can be seen, I am using a join for RecipeID, but I want it to use the 1st function I have (let's call it function1) that gets the ID instead. How do I go about this?

CREATE FUNCTION AveragePriceforRecipe() RETURNS @PriceTable TABLE (
  [Amount] smallmoney,
  [RecipeName] nvarchar(75)) AS
BEGIN
  INSERT INTO @PriceTable
       SELECT AVG(Amount)
        FROM Prices JOIN
             RecipePrices ON Prices.PriceID = RecipePrices.PriceID JOIN
             Recipes ON RecipePrices.RecipeID = Recipes.RecipeID
END

stackoverflow.com/questions/3205786/…

Vanarajan
– Vanarajan

2015-11-24 06:56:58 +00:00
Commented Nov 24, 2015 at 6:56 — Vanarajan
– Vanarajan, Commented Nov 24, 2015 at 6:56

t1t1an0 · Accepted Answer · 2015-11-24 07:02:43Z

are you using ms sql server (t-sql)? if so, please try the code below:

given that function1 is:

CREATE FUNCTION function1(@RecipeName)
RETURNS INT
AS
BEGIN
    DECLARE @RecipeID INT

    SELECT @RecipeID = ID
    FROM Recipes
    WHERE RecipeName = @RecipeName

    RETURN @RecipeID
END

getting the recipes price table using function1:

CREATE FUNCTION AveragePriceforRecipe (@Amount SMALLMONEY, @RecipeName NVARCHAR(75))
RETURNS @PriceTable TABLE
AS
BEGIN
    INSERT INTO @PriceTable
    SELECT AVG(Amount)
    FROM Prices
        JOIN RecipePrices ON Prices.PriceID = RecipePrices.PriceID
            AND RecipePrices.RecipeID = function1(@RecipeName)
END

Collectives™ on Stack Overflow

SQL Function within Function

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related