Set Variable in SQL Function with Condition

Question

Trying to set a varable based on the current date and pass to query wihtin SQL function. It error at the IF statement. Any ideas?

  CREATE FUNCTION CS_AwaredRCPs
     (   
     @currentDate DATE,
    @fiscalYear INT

    IF DATEPART(m,@currentDate) > 10
        SET @fiscalYear = DATEPART(yyyy,@currentDate)
    ELSE
        SET @fiscalYear = DATEPART(yyyy,@currentDate) - 1
    END 
     )
    RETURNS TABLE 
    AS
    RETURN 
    (
    SELECT      dbo.tbl_requirementManagement.postaward_specialist_id, 
            SUM(dbo.tbl_requirementManagement.actual_award_value) AS    AwardValue, 
            COUNT(dbo.tbl_requirementManagement.postaward_specialist_id) AS AwardCount
    FROM        dbo.tbl_requirementManagement RIGHT OUTER JOIN
            dbo.vw_ContractSpecialists ON   dbo.tbl_requirementManagement.postaward_specialist_id = dbo.vw_ContractSpecialists.user_certificateSerialNumber
    GROUP BY    dbo.tbl_requirementManagement.statusID, dbo.tbl_requirementManagement.postaward_specialist_id, dbo.tbl_requirementManagement.fiscal_year
    HAVING     (dbo.tbl_requirementManagement.statusID = 4) AND 
           (dbo.tbl_requirementManagement.postaward_specialist_id <> 0) AND 
           (dbo.tbl_requirementManagement.fiscal_year = @fiscalYear)

    )

`

Why are you passing in @FiscalYear when you don't use it? The IF / ELSE assigns it in all cases so the value passed in for @FiscalYear is never actually used. — Solomon Rutzky
– Solomon Rutzky, Commented Jan 15, 2015 at 16:57

GarethD · Accepted Answer · 2015-01-15 16:58:22Z

4

You don't need to use IF at all, you can simply make your predicate:

fiscal_year = DATEPART(YEAR, @currentDate) 
                + CASE WHEN DATEPART(MONTH, @CurrentDate) <= 10 THEN -1 ELSE 0 END

Then you don't need the @FiscalYear parameter at all. For what it is worth there are a number of other things you should change in your function

Use table aliases to significantly reduce the amount of text, so rather than using dbo.tbl_requirementManagement over and over again, you could just use rm
The fact that you reference your outer table in the where clause, turns your RIGHT OUTER JOIN into an INNER JOIN so you may as well just use and INNER JOIN. To clarify, you have (dbo.tbl_requirementManagement.statusID = 4), so where you have no match in tbl_requirementManagement, statusID will be NULL, and NULL = 4 returns NULL, which is not true, therefore the row would not be returned.
Your predicates do not reference aggregates so should be in the WHERE clause, not the HAVING

So your final function becomes:

CREATE FUNCTION CS_AwaredRCPs (@currentDate DATE)
RETURNS TABLE
AS
RETURN
(   SELECT  rm.postaward_specialist_id,
            SUM(rm.actual_award_value) AS AwardValue, 
            COUNT(rm.postaward_specialist_id) AS AwardCount
    FROM    dbo.tbl_requirementManagement AS rm
            INNER JOIN dbo.vw_ContractSpecialists AS cs
                ON rm.postaward_specialist_id = cs.user_certificateSerialNumber 
    WHERE   rm.fiscal_year = DATEPART(YEAR,@currentDate) 
                                + CASE WHEN DATEPART(MONTH, @CurrentDate) <= 10 THEN -1 ELSE 0 END
    AND     rm.statusID = 4
    AND     rm.postaward_specialist_id <> 0
    GROUP BY rm.statusID, rm.postaward_specialist_id, rm.fiscal_year
);

answered Jan 15, 2015 at 16:58

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Solomon Rutzky Over a year ago

Very nice. I was about to post something similar, at least in terms of pointing out the uselessness of the @FiscalYear parameter and keeping the entire thing as an Inline TVF. But your additional points are spot-on, so +1 instead :)

RockDad Over a year ago

-Gareth Thank you for your help and explanation. I am new to SQL functions and this certainly helps!

GarethD Over a year ago

No problem. One thing to bear in mind, as has been alluded to in another comment is that what you originally had, an inline table valued function (TVF) will perform much better than a multi-statement TVF (as in the accepted answer). The reason for this is that an inline TVF behaves more like a view, in that it's definition is expanded out into the outer query whenever it is called, it can therefore be optimised, whereas a multi-statement TVF is executed RBAR

Pரதீப் · Accepted Answer · 2015-01-15 16:52:41Z

1

You cannot do a if-else check inside Function parameter list. Move the If-else part to the function body. Try this.

CREATE FUNCTION Cs_awaredrcps (@currentDate DATE)
RETURNS @ret_table TABLE (
  postaward_specialist_id INT,
  AwardValue              INT,
  AwardCount              INT )
AS
  BEGIN
      DECLARE @fiscalYear INT

      IF Datepart(Month, @currentDate) > 10
        SET @fiscalYear = Datepart(yyyy, @currentDate)
      ELSE
        SET @fiscalYear = Datepart(yyyy, @currentDate) - 1

      INSERT INTO @ret_table
      SELECT dbo.tbl_requirementManagement.postaward_specialist_id,
             Sum(dbo.tbl_requirementManagement.actual_award_value)        AS AwardValue,
             Count(dbo.tbl_requirementManagement.postaward_specialist_id) AS AwardCount
      FROM   dbo.tbl_requirementManagement
             RIGHT OUTER JOIN dbo.vw_ContractSpecialists
                           ON dbo.tbl_requirementManagement.postaward_specialist_id = dbo.vw_ContractSpecialists.user_certificateSerialNumber
      GROUP  BY dbo.tbl_requirementManagement.statusID,
                dbo.tbl_requirementManagement.postaward_specialist_id,
                dbo.tbl_requirementManagement.fiscal_year
      HAVING ( dbo.tbl_requirementManagement.statusID = 4 )
             AND ( dbo.tbl_requirementManagement.postaward_specialist_id <> 0 )
             AND ( dbo.tbl_requirementManagement.fiscal_year = @fiscalYear )

      RETURN
  END

answered Jan 15, 2015 at 16:52

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

1 Comment

Sean Lange Over a year ago

This would be much better from a performance standpoint if it was an inline table valued function instead of a multi statement table valued function. Just changing this to a case expression instead of the IF at the beginning would greatly increase performance.

Collectives™ on Stack Overflow

Set Variable in SQL Function with Condition

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related