0

I have created a Scalar UDF as follows:

DROP FUNCTION IF EXISTS fnDiscountPrice
    GO
    CREATE FUNCTION fnDiscountPrice
        (@Item_ID int)
        RETURNS MONEY
    BEGIN
        DECLARE @Discount_Price money
        SELECT @Discount_Price = ItemPrice - DiscountAmount
        FROM OrderItems
WHERE ItemID = @Item_ID
    RETURN @Discount_Price
    END
    GO

I am trying to use this function to create another function here:

DROP FUNCTION IF EXISTS fnItemTotal
GO
CREATE FUNCTION fnItemTotal
    (@Item_ID int)
    RETURNS MONEY
BEGIN
DECLARE @Item_Total MONEY

SELECT @Item_Total = (dbo.fnDiscountPrice(ItemID) * Quantity)
FROM OrderItems
WHERE ItemID = @Item_ID
RETURN @Item_Total
END
GO
SELECT ItemID, ItemPrice, DiscountAmount, Quantity,
    dbo.fnDiscountPrice(ItemId) AS DiscountPrice, dbo.fnItemTotal(ItemID) AS ItemTotal
FROM OrderItems

My problem is that the fnDiscountPrice function is giving me an error "Cannot find either column "dbo" or the user-defined function or aggregate 'dbo.fnDiscountPrice', or the name is ambiguous."

I am not sure what the issue is here. The function exists in the programmability folder in database.

Any ideas?

MycoNut
  • 23
  • 5
  • There is no GO in mysql are you sure this isn't sqlserver? – P.Salmon Nov 09 '21 at 15:40
  • OH crap ya its SQLServer my bad. ill update quetion. – MycoNut Nov 09 '21 at 15:43
  • 1
    I find both of your functions quite suspect since neither references their `@Item_ID` parameter. – Damien_The_Unbeliever Nov 09 '21 at 15:49
  • 1
    As you don't schema qualify them when you create them they could have ended up in some other schema than `dbo`. You should use `CREATE FUNCTION dbo.fnItemTotal` etc – Martin Smith Nov 09 '21 at 15:54
  • I doubt either of those functions are going to work as you expect, unless your table `OrderItems` can only hold one row and **only ever** one row. And if that's the case, `OrderItems` is a some what silly name for something that only contains a **single** item. – Thom A Nov 09 '21 at 15:56
  • Ok I fixed the reference to @Item_ID in both functions I think(Edited question). Also, in the programmability folder it shows it saved as dbo.fnDiscountPrice. Doesn't that mean its saved in the correct schema? – MycoNut Nov 09 '21 at 16:02
  • Are you seeing this error as an intellisense warning in SSMS or when you actually execute the SQL? – Martin Smith Nov 09 '21 at 16:04
  • I'm seeing it as an intellisense warning. I just tried executing it and it worked.. Not sure why I am getting this warning. – MycoNut Nov 09 '21 at 16:05
  • Might be just an intellisense thing then. Maybe hasn't updated its object lists. – RBarryYoung Nov 09 '21 at 16:16
  • 1
    Update Intellisense with Ctrl + Shift + R. Side point: you should avoid UDFs in favour of *inline* Table Valued Functions. One UDF calling another UDF is just the height of bad performance – Charlieface Nov 09 '21 at 19:44

0 Answers0