CODEWITHSUNDEEP
sqlsql-servert-sqlsql-function
MiaPro
MiaPro

Reputation: 11

SQL Function within Function

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

Upvotes: 0

Views: 76

Answers (1)

t1t1an0
t1t1an0

Reputation: 281

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

Upvotes: 1

Related Questions