Using An IF Statement in a Function

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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