CODEWITHSUNDEEP
sql-serversql-function
Richard
Richard

Reputation: 7433

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

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.

Upvotes: 1

Views: 290

Answers (3)

Squirrel
Squirrel

Reputation: 24773

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)

Upvotes: 2

Sanal Sunny
Sanal Sunny

Reputation: 617

Use the below function

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

    );

Upvotes: 1

Dale K
Dale K

Reputation: 27287

You can't use procedural logic inside an ITVF, rather you can only use a query of some sort which returns a result set.

Reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

The following is hopefully close to what you are looking for:

CREATE FUNCTION ufnTotalSales
(
  @StartDate datetime
  , @EndDate datetime
  , @FoodName nvarchar(50)
)
RETURNS TABLE
AS
RETURN
  SELECT f.FoodID, FoodName, (FoodPrice * Quantity) AS TotalSales FROM Food f, OrderFoodRel ofr
  WHERE (Date_Time BETWEEN @StartDate AND coalesce(@EndDate,getdate()))
  and coalesce(@FoodName,'') = ''
  union all
  SELECT f.FoodID, FoodName, (FoodPrice * Quantity) AS TotalSales FROM Food f, OrderFoodRel ofr
  WHERE (Date_Time BETWEEN @StartDate AND coalesce(@EndDate,getdate()))
  AND coalesce(@FoodName,'') = FoodName

Upvotes: 1

Related Questions