CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-function
Daniel Stallard
Daniel Stallard

Reputation: 79

How to call a function in another Function

I need to write a script that creates and calls a function named fnItemTotal that calculates the total amount of an item in the OrderItems table (discount price multiplied by quantity).

To do that, this function should accept one parameter for the item ID, it should use the DiscountPrice function that I created earlier and it should return the value of the total for that item.

This is my Function I created and it gets created. But when i try to call it it gives an error.

CREATE FUNCTION fnItemTotal 
        (@ItemID INT )
    RETURNS MONEY
BEGIN
RETURN 
    (
        SELECT 
            ItemId,
            (SELECT * FROM dbo.fnDiscountPrice(ItemID) WHERE ItemID=@ItemID)*Quantity)--The fnDiscountPrice is the Other function i created.
        FROM 
            OrderItems
        WHERE 
            ItemID=@ItemID
    );
END 
GO

This is what I'm using to call it:

Select ItemID,dbo.fnItemTotal(ItemID) AS 'Total Price'
from OrderItems 
ORDER BY ItemID;

This is the ERROR it gives me when I call it:

Msg 208, Level 16, State 3, Line 2 Invalid object name 'dbo.fnDiscountPrice'.

Upvotes: 4

Views: 28112

Answers (1)

Hadi
Hadi

Reputation: 37313

Try using the following code:

CREATE FUNCTION dbo.fnItemTotal 
    (@ItemID INT )
RETURNS MONEY
BEGIN
DECLARE @X as MONEY 

        SELECT @X = dbo.fnDiscountPrice(ItemID) *  OrderItems.Quantity 
        FROM 
            OrderItems
        WHERE 
            OrderItems.ItemID=@ItemID

            RETURN @X

END

Upvotes: 5

Related Questions