If Else statement in Table Valued function

Question

I have a table

ProdID, ProdName, Price

I want to use if else statement in table valued function to make it so that if Qty is over or equal 10, I would give a 10 percent discount else no discount. This is my code.

Create function FDiscount (@ProdName varchar(50), @Qty int)
Returns Table
as
return 
    IF (@Qty >= 10)
        Select ProdName, Price, @Qty as Qty, Price * @Qty as Total, 
        (Price * @Qty) - (Price * @Qty) /100 * 10 as Discount10
        from TblProduct
    Else
        Select ProdName, Price, @Qty as Qty, Price * @Qty as Total
        from TblProduct
 Where ProdName = @ProdName
 select * from FDiscount('Milk','10')

But it said "Incorrect syntax near the keyword 'IF', I can't seem to find any solution to this.

score 1 · Answer 1 · edited Nov 08 '19 at 11:01

1

you can read this tsql returning a table from a function or store procedure

and use this

Create function FDiscount (@ProdName varchar(50), @Qty int)
Returns @tblTable(ProdName VARCHAR(100), Price INT, Qty INT, Discount10 INT)
as
BEGIN
    IF @Qty >= 10
         INSERT INTO @tbl
         Select ProdName, Price, @Qty , Price * @Qty, 
        (Price * @Qty) - (Price * @Qty) /100 * 10   
        from TblProduct
    Else
        INSERT INTO @tbl
         Select ProdName, Price, @Qty , Price * @Qty , 0
        from TblProduct
 Where ProdName = @ProdName
 return
END

edited Nov 08 '19 at 11:01

Peter Morris

20,174
9
81
146

answered Jul 22 '17 at 16:34

Amit Kumar Singh

4,393
2
9
22

This is what the program said "A RETURN statement with a return value cannot be used in this context". – Raksa Jul 22 '17 at 16:39

score 1 · Answer 2 · answered Jul 22 '17 at 16:59

1

Why not a simple CASE statement

CREATE FUNCTION Fdiscount (@ProdName VARCHAR(50), 
                           @Qty      INT) 
returns TABLE 
AS 
    RETURN 
      (SELECT prodname, 
              price, 
              @Qty         AS Qty, 
              price * @Qty AS Total, 
              CASE 
                WHEN @Qty >= 10 THEN ( price * @Qty ) - ( price * @Qty ) / 100 * 10 
                ELSE 0 
              END          AS Discount10 
       FROM   tblproduct 
       WHERE  prodname = @ProdName)

answered Jul 22 '17 at 16:59

Pரதீப்

91,748
19
131
172

But is it possible with IF statement? – Raksa Jul 22 '17 at 17:06
1

@Raksa - Yes we can.. using Multiline table valued function but why ? Performance wise Inline will be faster than multline function.. Don't go for it – Pரதீப் Jul 22 '17 at 17:11

score 0 · Answer 3 · answered Jul 22 '17 at 17:03

Two things... #1 Functions must return the same number of columns no matter what parameter values are supplied. So, you can't toggle the discount column on & off based on @Qty. #2 Multi-statement functions (mTVF) tend to perform horribly. You should attempt to make your function an inline function (iTVF). See the following as a suggested rewrite...

CREATE FUNCTION dbo.FDiscount (@ProdName varchar(50), @Qty int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN 
    SELECT 
        p.ProdName, 
        p.Price, 
        Qty = @Qty,
        Total = p.Price * @Qty,
        Discount10 = IIF(@Qty >= 10, (p.Price * @Qty) - (p.Price * @Qty) / 100 * 10, 0)
    FROM 
        dbo.TblProduct p
    WHERE 
        p.ProdName = @ProdName;
GO

HTH, Jason

If Else statement in Table Valued function

3 Answers3