Conditional CROSS APPLY in SQL Server

Question

I have the following query which works perfectly fine:

Version #1

SELECT 
    t.ScheduleId,t.BaseDate,t.AfterDate,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY 
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, t.AfterDate, 0, 1, NULL, NULL, NULL) fn

Further, I did the following changes to the above query which also works fine:

Version #2

SELECT 
    t.ScheduleId, t.BaseDate, t.AfterDate, t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY 
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, COALESCE(t.PreviousBillingDate, t.BaseDate), 0, 2, NULL, NULL, NULL) fn
WHERE
    fn.RowNumber = 2

The question

Now, based on a certain condition I want to pass different args in the user defined function dbo.fn_ScheduleCalculator, but I am not able to get the syntax right.

Here's what I am trying to achieve:

SELECT 
    t.ScheduleId, t.BaseDate, t.AfterDate, t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY
IF ((COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()) 
    OR (t.BaseDate <> GETDATE() and t.FromBilling = 0))

    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, COALESCE(t.PreviousBillingDate, t.BaseDate), 0, 2, NULL, NULL, NULL) fn
    WHERE
        fn.RowNumber = 2
ELSE
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, t.AfterDate, 0, 1, NULL, NULL, NULL) fn)

I understand that without sharing some data for the above tables it will be hard to suggest the exact syntax on how to achieve what I am looking for, but even if someone can suggest how I can achieve what I am looking for or if any issues in the syntax that you see and can point out will be of great help.

Here:

dbo.fn_ScheduleCalculator: is a user defined function

Distinct_BillableMemberAgreementItems_CTE: is a CTE

Answer 1

You can use a CROSS APPLY with a virtual table to do some calculations, then pass those to the function.

I don't know the meaning of the calculations, so I've just give them generic names. I also don't know the correctness of your logic, I've just copied what you've shown.

SELECT 
    t.ScheduleId,
    t.BaseDate,
    t.AfterDate,
    t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t

CROSS APPLY (
    SELECT
        CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
               OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
          THEN ISNULL(t.PreviousBillingDate, t.BaseDate)
          ELSE t.AfterDate
        END,
        CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
               OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
          THEN 2
          ELSE 1
        END
) v1(SomeCalculation1, SomeCalculation2)

CROSS APPLY dbo.fn_ScheduleCalculator(
    t.ScheduleId,
    t.BaseDate,
    v1.SomeCalculation1,
    0,
    v2.SomeCalculation2,
    NULL, NULL, NULL
  ) fn
WHERE
    (v1.SomeCalculation2 = 1 OR fn.RowNumber = 2);