locked
Using An IF Statement in a Function RRS feed

  • Question

  • This should be fairly simple... all I want to do is write a function that returns a table.  I have that part down pat, but I want to return the data differently depending on a variable that is passed to the function - @int_Level.  If @int_Level=1, return such-and-such SQL, if @int_Level=2 return such-and-such SQL.

    Without the IF statement the following works fine:

    IF
    @int_Level = 5

    BEGIN

    RETURN

          SELECT Est_ID, CC_QuickID, CC_ID, CC_L1, CC_L2, CC_L3, CC_L4, CC_Description, ED_GrandTotal

          FROM tblEstimates INNER JOIN tblCostCodes ON tblEstimates.Est_CCS_ID = tblCostCodes.CC_CCS_ID INNER JOIN tblEstimateDetail ON tblCostCodes.CC_ID = tblEstimateDetail.ED_CC_ID

          WHERE Est_ID = @int_ID

    END

    With the IF statement I cannot create the function - it complains that there is an error near 'IF'.

    What am I doing wrong?  Is it possible for me to do this with functions, or am I looking in the wrong direction?

    Thank you very much for your help.

    Dustin

    Tuesday, December 15, 2009 1:13 AM

Answers

All replies

  • Dustin,

    Without the "if" you can build a table-valued INLINE function the way you are doing it with a single RETURN.

    If you have control flow logic, you cannot do it that way. You have to declare a @tablevalued function before the "AS".

    Examples: http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Naomi N Tuesday, December 15, 2009 4:06 AM
    • Marked as answer by Kalman Toth Sunday, January 15, 2012 12:38 AM
    Tuesday, December 15, 2009 1:46 AM
  • In addition to SQLUSA's excellent answer, please be aware that a multi-statement table-valued function can cause a huge performance hit, whereas an inline table-valued function typically only causes a minor performance degradation.

    So if at all possible, I would advice you to try if you can use CASE to combine the different requirements in a single RETURN statement - unless this query will be used in a situation where performance is less important (smalll tables, or batch process in a huge maintenance window) and ease of maintenance is very important (lots of changes with little time to make the change).
    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 15, 2009 9:00 AM
  • In the simplest case:

    SELECT ... WHERE @int_Level = 1
    UNION ALL
    SELECT ... WHERE @int_Level = 5

    Although, as Hugo pointed out, a CASE statement might be better. It all depends on the statements.
    Tuesday, December 15, 2009 2:33 PM
  • Thank you very much for everyone's help... your suggestions put me down the correct path, so that I skipped the whole use of a functions in favor of using a single stored procedure.  I was just making everything alot more complicated that it had any right to be.

    Dustin
    Wednesday, December 30, 2009 5:16 PM