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.

@SanalSunny They are related using the primary key from Food (FoodID). OrderFoodRel has a foreign key FoodID that points to said primary key. — Richard, Nov 01 '18 at 06:20

Dale K · Answer 1 · 2018-11-01T06:19:35.880

1

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

edited Nov 01 '18 at 06:19

answered Nov 01 '18 at 06:09

Dale K

25,246
15
42
71

https://stackoverflow.com/questions/8358315/t-sql-function-with-default-parameters There is a post about default parameters, though. Or does that only work with scalar-valued functions? – Richard Nov 01 '18 at 06:17
1

You're correct sorry - you can have defaults, but the default has to be a static value, not a function call like getdate(). But I would do it the way I have and pass null in or set the default to null if you don't want to have to pass the parameter in. – Dale K Nov 01 '18 at 06:20
If I want the @EndDate to be the datetime of now and I call the function without that parameter, won't it return the same error as problem in said link ('An insufficient number of parameters were supplied')? – Richard Nov 01 '18 at 06:23
1

Sorry you're (sort of) right again, best to read the reference link as it tells you exactly how to do it. You could set defaults (of say null) and then when you call the function you have to pass the keyword `default` in instead of a value. Personally I would just pass null in and not use defaults - but up to you. – Dale K Nov 01 '18 at 06:30
I see, thank you for the answer! How did you know that I can't use procedural logic inside an ITVF? Did it come with experience or is there an explicit explanation of that rule? – Richard Nov 01 '18 at 06:36
If you check the reference docs (the link I posted) under ITVF it says `RETURN [ ( ] select_stmt [ ) ] ` and then further down it defines what `select_stmt` is. – Dale K Nov 01 '18 at 06:40
I see, thank you again! Unfortunately, I've chosen to accept another answer. Nevertheless, yours helped me a lot in understanding what I got wrong, too. Have a great day. – Richard Nov 01 '18 at 06:41

Sanal Sunny · Answer 2 · 2018-11-01T06:22:57.283

1

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

    );

edited Nov 01 '18 at 06:22

answered Nov 01 '18 at 06:12

Sanal Sunny

617
3
9

That doesn't help with the fact that there is procedural logic within an ITVF though. – Dale K Nov 01 '18 at 06:15
@WealthyPlayer please try now – Sanal Sunny Nov 01 '18 at 06:23

Squirrel · Accepted Answer · 2018-11-01T06:32:33.150

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)

edited Nov 01 '18 at 06:32

answered Nov 01 '18 at 06:23

Squirrel

23,507
4
34
32

However, there is a post in regard to assigning a parameter a default value here: https://stackoverflow.com/questions/8358315/t-sql-function-with-default-parameters – Richard Nov 01 '18 at 06:24
1

edited the query. OK. it does not like `getdate()` as default value. Only constant – Squirrel Nov 01 '18 at 06:26
For the last part FoodName = '' OR f.FoodName = FoodName, is it correct if I say that that last part means: if FoodName is an empty string (the default value), then any FoodName fulfills that condition; buat if it isn't, check the second condition which is comparing the FoodName from my Food table with the parameter inserted (which is no longer an empty string)? – Richard Nov 01 '18 at 06:31
Also, how do I set so that if the user opts to not use specify the EndDate, it will default to the datetime of now? Is it using COALESCE as the other answers have provided? I think that it's correct since COALESCE will always choose the GETDATE() option when EndDate is not specified, I guess I'm asking for reassurance. – Richard Nov 01 '18 at 06:32
1

edited the answer to include that. Yes `ISNULL()` and `COALESCE()` works the same in this case – Squirrel Nov 01 '18 at 06:34
Okay, thank you! As for the first part of my question, regarding the last part of FoodName = '' OR f.FoodName = FoodName. Is my logic correct? – Richard Nov 01 '18 at 06:37
yes. your logic / understanding on that is correct – Squirrel Nov 01 '18 at 06:38
Thank you :-)! I understand what I got wrong now. How did you know that I'm not allowed to use IF ... ELSE inside a function? Is there an official explanation in regard to that matter? – Richard Nov 01 '18 at 06:39
`mposes conditions on the execution of a Transact-SQL statement` https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-2017 . Your function defined as `RETURNS TABLE`. So the `RETURN` portion should be a query that returns a single result. Hence not allow any flow control logic. If you do required any conditional checking use `CASE WHEN` – Squirrel Nov 01 '18 at 06:53
1

Or to be more specific, you can't use `IF..ELSE` in table value function, it is fine in scalar function – Squirrel Nov 01 '18 at 07:03

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

3 Answers3