CODEWITHSUNDEEP
sql-server
user6477547
user6477547

Reputation:

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

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)

Upvotes: 1

Views: 382

Answers (3)

IVNSTN
IVNSTN

Reputation: 9309

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?

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58715

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
  )

Upvotes: 2

artm
artm

Reputation: 8584

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

Upvotes: 2

Related Questions