I have been trying to get this code to work, and it just won\'t work. Can someon

Question

I have been trying to get this code to work, and it just won't work. Can someone please help me?

3. 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 you created in exercise 2, and it should return the value of the total for that item.

This is the code I wrote for the DiscountPrice function from Exercise 2.:

USE MyGuitarShop

GO

CREATE FUNCTION fnDiscountPrice
   (@ItemID int)
   RETURNS money

BEGIN
   RETURN (
       SELECT
           (ItemPrice - DiscountAmount) AS DiscountPrice
               FROM OrderItems
           WHERE
               ItemID = @ItemID);
END;

GO

PRINT
   'The Discount Price is: $' + CONVERT(varchar, dbo.fnDiscountPrice(2));

Explanation / Answer

use MyGuitarShop;
go
/*scalar function means returns one regular value */
/*table value functions means that what gets returned
by the function is a table */

if exists (select DB_ID('fnItemTotal'))
   drop function fnItemTotal;
go
create function fnItemTotal (@ItemIdDiscount int)
returns money
begin
   declare @TotalItemAmount money;
      
       select @TotalItemAmount = dbo.fnDiscountPrice(ItemID)*oi.Quantity
       from OrderItems oi
       where oi.ItemID = @ItemIdDiscount
  
   return @TotalItemAmount
end;
go

select itemid, dbo.fnDiscountPrice(itemid) as discountprice, dbo.fnItemTotal(itemid) as ItemTotal
from orderitems;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.