Create a function to calculate the cost of an order SQL Server

1k views Asked by At

I am trying to create a function that will calculate the the OrderCost based on this formula

((1.0 – Discount) * (UnitPrice * Quantity))

Not sure where I am going wrong here.. I get this error message when trying to run the query

Msg 102, Level 15, State 1, Procedure OrderCost, Line 7 [Batch Start Line 0]
Incorrect syntax near '–'.

enter image description here

CREATE FUNCTION [dbo].[OrderCost] (@i INT)
RETURNS int
AS
BEGIN
    DECLARE @OrderCost INT

    SELECT @OrderCost = SUM((1.0 – Discount) * (UnitPrice * Quantity))
    FROM OrderDetails

    RETURN @OrderCost
END
2

There are 2 answers

1
Chanukya On BEST ANSWER

Change the minus operator '–' to '-'

 CREATE FUNCTION [dbo].[OrderCost](@i int)
    RETURNS int
    AS
    BEGIN
        DECLARE @OrderCost int

        SELECT @OrderCost = SUM((1.0 - Discount) * (UnitPrice * Quantity))
        FROM OrderDetails

        RETURN @OrderCost
    END
0
Raj More On

What if you have 20 orders in there? You need to know which one to pick!

CREATE FUNCTION [dbo].[OrderCost](@i int, @OrderId Int, @ProductId Int)
RETURNS int
AS
BEGIN
    DECLARE @OrderCost int

    SELECT @OrderCost = SUM((1.0 - Discount) * (UnitPrice * Quantity))
    FROM OrderDetails
    Where OrderId = @OrderId and ProductId = @ProductId

    RETURN @OrderCost
END