Errors while Trying to Use a Function Parameter inside an IF Statement

Question

I have the following CREATE FUNCTION:

CREATE FUNCTION ufnTotalSales (@StartDate datetime, @EndDate datetime = GETDATE(), @FoodName nvarchar(50) = '')
RETURNS TABLE
AS
    RETURN
    (
        IF @FoodName = '';
        BEGIN
            SELECT f.FoodID, FoodName, (FoodPrice * Quantity) AS TotalSales FROM Food f, OrderFoodRel ofr
            WHERE (Date_Time BETWEEN @StartDate AND @EndDate)
        END

        ELSE
        BEGIN
            SELECT f.FoodID, FoodName, (FoodPrice * Quantity) AS TotalSales FROM Food f, OrderFoodRel ofr
            WHERE (Date_Time BETWEEN @StartDate AND @EndDate) AND @FoodName = FoodName
        END
    );

The first error occurs at @EndDate datetime = GETDATE(), it says Incorrect syntax near '()'. I'm trying to assign the @EndDate parameter a default value of the current datetime if the user opts to use the default value, but somehow I get an error.

The second error occurs at all the parameters that I used in IF ... ELSE block (@FoodName, @StartDate and @EndDate). It says that I Must declare the scalar variable "@...". It's a parameter and not a scalar variable, how do I fix this?

The idea of this function is to return the total sales of food(s) with two options: one being the total sales of a food with name X from a date to another date if you specified the food name; and two being the total sales of food(s) from a date to another date disregarding the food name.

Answer 1

there are several issue with the function

Firstly, you can't have default value assign to functionyou can't use IF .. ELSE inside a function Secondly. the tables Food and OrderFoodRel are not JOINed. You are doing a cross join here

It doesn't like getdate() as default value, you can set NULL as default for the @EndDate and use ISNULL() on the @EndDate

this is will create the function. I assumed the 2 tables are related by FoodID

CREATE FUNCTION ufnTotalSales 
(
    @StartDate datetime,  
    @EndDate   datetime     = NULL, 
    @FoodName  nvarchar(50) = ''
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT  f.FoodID, FoodName, (FoodPrice * Quantity) AS TotalSales 
        FROM    Food f
                INNER JOIN OrderFoodRel ofr on  f.FoodID    = ofr.FoodID
        WHERE   Date_Time BETWEEN @StartDate AND ISNULL(@EndDate, GETDATE())
        AND     (
                    @FoodName   = ''
                OR  f.FoodName  = @FoodName
                )
    );
GO

So to use the default value on the input, you need to use the keyword default

select  *
from    dbo.ufnTotalSales('2018-10-01', default , default)