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

The thing is that I want to patch this code for a certain set of records that are having issues with this code, for the rest of the records the entire script is running fine. Hence if the record matches the condition mentioned inside the 'if' condition I want to run my fix, ELSE keep running the old code. With Union, I am not sure what will be the condition in else condition so not a feasible option. — er.animesh, Aug 24 '22 at 17:20
You need to use two queries with a UNION ALL. There is no ELSE, that is used in logical operators to control flow. — Sean Lange, Aug 24 '22 at 17:51
If you want to use a conditional statement, it needs to be [outside the query](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver16), either in your "script" or in a stored procedure. If (condition x) query1 ELSE query2 — devlin carnate, Aug 24 '22 at 18:00

Charlieface · Accepted Answer · 2022-08-26T10:32:58.037

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);

I initially did not understand this, but now I do. This one worked like a charm. In place of v.SomeCalculations1 and v.SomeCalculation2 it should be v1.SomeCalculations1 and v1.SomeCalculation2. I am not able to edit your answer, please edit and I will accept this answer. Thanks a lot for taking out time and helping, appreciate it. — er.animesh, Aug 26 '22 at 07:49

score 0 · Answer 2 · answered Aug 24 '22 at 17:59

With the clarification on the requirements you need to use two queries and UNION ALL to merge those two queries together. Following the current requirements it would be something like this.

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 
(
    COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
    OR 
    (
        t.BaseDate <> GETDATE() 
        and 
        t.FromBilling = 0
    )
)
AND fn.RowNumber = 2

UNION ALL

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, t.AfterDate, 0, 1, NULL, NULL, NULL) fn
where NOT
(
    COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
    OR 
    (
        t.BaseDate <> GETDATE() 
        and 
        t.FromBilling = 0
    )
)

Appreciate your help. Looks like it should work, will test and get back but is there a better way of doing this than using union? Consider this script running against a million records + this is part of an even larger script that adds up to the execution time. Correct me if I am wrong, so far what I have understood about cross apply is that it will run each record against what comes after cross apply. So I am assuming running 2 queries(and later doing the union) will do the same operation on a million records twice while if I can do it in a single statement it should offer better performance. — er.animesh, Aug 25 '22 at 15:22
Well you truly have two different datasets you are trying to deal with. And in one of those you need the return value from the function to even know which rows to return. Not sure there is much you can do here. — Sean Lange, Aug 25 '22 at 15:26
Somehow the query after UNION ALL condition is not working, if I comment the entire query after UNION ALL I get the expected results but I want an else/case condition to handle the remaining scenarios. Thanks! — er.animesh, Aug 26 '22 at 07:58

Conditional CROSS APPLY in SQL Server

2 Answers2