SQL Server: Function That Returns a Table Using IF and ELSE

Question

I'm pretty new to SQL Server, so bear with me. All I'm trying to do is create a function that returns a table if a certain condition is met, the condition being the discount amount being greater than zero. If the discount amount is not greater than zero, then another table should be returned. I keep receiving the following errors though:

Incorrect syntax near the keyword 'IF'," and "a RETURN statement with a return value cannot be used in this context.

Any help would be appreciated. Here's my code so far:

IF OBJECT_ID(N'dbo.fn_PopulateDiscountTable', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_PopulateDiscountTable;
GO

CREATE FUNCTION dbo.fn_PopulateDiscountTable(@FolioID smallint)
RETURNS TABLE
AS
    IF((SELECT D.DiscountAmount 
        FROM Discount D, Folio F 
        WHERE D.DiscountID = F.DiscountID 
          AND FolioID = @FolioID) > 0)
        RETURN (SELECT F.QuotedRate AS "QuotedRate", D.DiscountAmount AS "DiscountAmount"
                FROM Discount D, Folio F
                WHERE D.DiscountID = F.DiscountID
                  AND FolioID = @FolioID)
    ELSE
        RETURN(SELECT F.QuotedRate AS "QuotedRate", D.DiscountPercent AS "DiscountPercent"
               FROM Discount D, Folio F
               WHERE D.DiscountID = F.DiscountID
                 AND FolioID = @FolioID)

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jul 07 '16 at 04:32

score 2 · Answer 1 · answered Jul 07 '16 at 00:38

You can define the table you will return and insert into that:

CREATE FUNCTION dbo.fn_PopulateDiscountTable(@FolioID smallint)
RETURNS @Result TABLE
(
     QuotedRate varchar(20), DiscountAmount int
)
AS
BEGIN
IF((SELECT D.DiscountAmount FROM Discount D, Folio F WHERE D.DiscountID = F.DiscountID AND FolioID = @FolioID) > 0)
Insert into @Result
SELECT F.QuotedRate AS "QuotedRate", D.DiscountAmount AS "DiscountAmount"
FROM Discount D, Folio F
WHERE D.DiscountID = F.DiscountID
AND FolioID = @FolioID

ELSE
Insert into @Result 
SELECT F.QuotedRate AS "QuotedRate", D.DiscountPercent AS "DiscountPercent"
FROM Discount D, Folio F
WHERE D.DiscountID = F.DiscountID
AND FolioID = @FolioID

Return --will return @Result
END

@artm nice option, I didn't think of that. – JosephStyons Jul 07 '16 at 00:47 — JosephStyons, Jul 07 '16 at 00:47

score 2 · Answer 2 · edited May 23 '17 at 12:01

You can't include branching logic in table valued functions, because from the compiler's point of view, that introduces uncertainty as to what the result schema will look like.

For example, you might decide to return a single column of strings with one branch, and twelve columns of xml from the other. The table-valued function must have a definite schema in order to be treated as a table. The rule you are bumping up against is what the compiler uses to guarantee to users of your function that they can count on the same result columns coming back every time.

In this case, however, you can move the logic to a CASE statement in the SELECT, as shown below.

Note: I also took the liberty of updating the joins to the more modern ANSI 92 syntax.

IF OBJECT_ID(N'dbo.fn_PopulateDiscountTable', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_PopulateDiscountTable;

GO

CREATE FUNCTION dbo.fn_PopulateDiscountTable(@FolioID smallint)
RETURNS TABLE
AS
RETURN(
  SELECT
    F.QuotedRate AS "QuotedRate"
   ,case when 0 < (
      SELECT D.DiscountAmount
      FROM
        Discount D
        inner join Folio F on f.discountid = d.discountid
      WHERE FolioID = @FolioID
      )
         then D.DiscountAmount
         else d.DiscountPercent
    end AS "Discount"
  FROM
    Discount D
    inner join Folio F on d.discountid = f.discountid
  WHERE f.FolioID = @FolioID
  )

...And I didn't think of this :) , too lazy to work out the query. — artm, Jul 07 '16 at 00:54

Ivan Starostin · Answer 3 · 2016-07-07T07:46:56.583

Actually simple CASE does all the work:

SELECT
  F.QuotedRate AS "QuotedRate", 
  CASE WHEN D.DiscountAmount > 0 THEN D.DiscountAmount ELSE D.DiscountPercend END AS "Discount"
FROM Discount D
INNER JOIN Folio F ON D.DiscountID = F.DiscountID
WHERE F.FolioID = @FolioID

But note, such a result does not make sense: amount and percent are not the same. In most scenarios it's impossible to replace one with another.

0.5, 1.25, 30.00 - which one is what? amount or percent?

SQL Server: Function That Returns a Table Using IF and ELSE

3 Answers3