How to use specific table in a join under condition?

Question

I want to write a stored procedure including input parameters, temp tables and joins.

I have AllocationDetails temp table in this procedure:

;WITH AllocationDetails AS
(
    SELECT
        lastPaymentAllocationDetails.*
    FROM   
        (SELECT 
             *, 
             ROW_NUMBER() OVER (PARTITION BY [PaymentAllocationGuid] ORDER BY [Year] DESC, [WorkingPeriodTitle] DESC) rowNumber
         FROM 
             ret_vwPaymentAllocationDetails) lastPaymentAllocationDetails
WHERE lastPaymentAllocationDetails.rowNumber = 1
),

Then I would like to have another temp table - like this:

ValidPaymentAllocations AS
(
    SELECT
        paymentAllocationDetails.[PaymentAllocationGuid],
        paymentAllocation.[RetiredPersonnelGuid],
        MAX(paymentAllocationDetails.ID) AS MaxPaymentAllocationDetailID,
        MAX(paymentAllocationDetails.[Year] * 100 + paymentAllocationDetails.[WorkingPeriodTitle]) AS [WorkingPeriodKey]
    FROM
        ret_PaymentAllocation AS paymentAllocation 
    INNER JOIN
        CASE when(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] 
    INNER JOIN
        ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] 
    INNER JOIN
        (SELECT MAX(ID) ID FROM ret_PaymentAllocationDetails GROUP BY PaymentAllocationGuid,WorkingPeriodTitle,Year) lastRecord ON LastRecord.Id = paymentAllocationDetails.ID
    WHERE
        ISNULL(paymentAllocation.[Deleted],0) = 0 AND
        paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
        (paymentAllocationDetails.[Year]  * 100 + paymentAllocationDetails.[WorkingPeriodTitle] <= @Year * 100 + @WorkingPeriodID AND paymentAllocationDetails.[Status] = 1 AND paymentTypes.[PaymentTypeFlag] <> 3 OR
         paymentAllocationDetails.[Year]  * 100 + paymentAllocationDetails.[WorkingPeriodTitle] = @Year * 100 + @WorkingPeriodID AND paymentTypes.[PaymentTypeFlag] = 3) AND
        (paymentAllocation.[PaymentCommandType] = @PaymentCommandType OR paymentAllocation.[PaymentTypesGuid] IN (SELECT * FROM com_udfSplit(@PaymentTypeGuids,','))) AND
        ((paymentTypes.[AffectOnAmountNature] = 1) OR (paymentTypes.[AffectOnAmountNature] = 2 AND paymentAllocation.[Amount] > 0))
    GROUP BY paymentAllocation.[RetiredPersonnelGuid], paymentAllocationDetails.[PaymentAllocationGuid]
),

But in join section, under one condition @PaymentCommandType = 1 I want to use ret_PaymentAllocationDetails instead "AllocationDetails"(the temp table above):

CASE WHEN(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid]

I used CASE WHEN but it didn't worked. How to handle this issue?

This is the whole procedure:

CREATE PROCEDURE [dbo].[ret_PayrollCalculations_GetPayrollItems]
(
    @SalariedGuid uniqueidentifier,
    @Year int,
    @WorkingPeriodID int,
    @PaymentCommandType int,
    @PaymentTypeGuids nvarchar(max),
    @PayrollItemGuid uniqueidentifier
)
 AS
IF(ISNULL(@PayrollItemGuid, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' AND @PaymentCommandType = 1)
    SELECT @PayrollItemGuid = [Guid] FROM ret_PayrollItemPatterns WHERE [PaymentCommandType] = 1 AND ISNULL([Deleted], 0) = 0 AND [Active] = 1
;WITH AllocationDetails
AS
(
    SELECT
        lastPaymentAllocationDetails.*
    FROM (
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY [PaymentAllocationGuid] ORDER BY [Year] DESC, [WorkingPeriodTitle] DESC) rowNumber
    FROM ret_vwPaymentAllocationDetails
) lastPaymentAllocationDetails
WHERE lastPaymentAllocationDetails.rowNumber = 1
), ValidPaymentAllocations AS(
    SELECT
        paymentAllocationDetails.[PaymentAllocationGuid],
        paymentAllocation.[RetiredPersonnelGuid],
        MAX(paymentAllocationDetails.ID) AS MaxPaymentAllocationDetailID,
        MAX(paymentAllocationDetails.[Year] * 100 + paymentAllocationDetails.[WorkingPeriodTitle]) AS [WorkingPeriodKey]
    FROM
        ret_PaymentAllocation AS paymentAllocation INNER JOIN
        CASE when(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] INNER JOIN
        ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] INNER JOIN
        (SELECT MAX(ID) ID FROM ret_PaymentAllocationDetails GROUP BY PaymentAllocationGuid,WorkingPeriodTitle,Year) lastRecord ON LastRecord.Id     = paymentAllocationDetails.ID
    WHERE
        ISNULL(paymentAllocation.[Deleted],0) = 0 AND
        paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
        (paymentAllocationDetails.[Year]  * 100 + paymentAllocationDetails.[WorkingPeriodTitle] <= @Year * 100 + @WorkingPeriodID AND paymentAllocationDetails.[Status] = 1 AND paymentTypes.[PaymentTypeFlag] <> 3 OR
         paymentAllocationDetails.[Year]  * 100 + paymentAllocationDetails.[WorkingPeriodTitle] = @Year * 100 + @WorkingPeriodID AND paymentTypes.[PaymentTypeFlag] = 3) AND
        (paymentAllocation.[PaymentCommandType] = @PaymentCommandType OR paymentAllocation.[PaymentTypesGuid] IN (SELECT * FROM com_udfSplit(@PaymentTypeGuids,','))) AND
        ((paymentTypes.[AffectOnAmountNature] = 1) OR (paymentTypes.[AffectOnAmountNature] = 2 AND paymentAllocation.[Amount] > 0))
    GROUP BY paymentAllocation.[RetiredPersonnelGuid], paymentAllocationDetails.[PaymentAllocationGuid]
), LastAmounts AS (
SELECT
    payrollCalculationDetails.[ItemGuid],
    SUM(payrollCalculationDetails.[Amount]) AS [Amount]
FROM
    ret_PayrollCalculationCommands payrollCalculationCommands INNER JOIN
    ret_PayrollCalculations payrollCalculations ON payrollCalculationCommands.[Guid] = payrollCalculations.[CalculationCommandGuid] INNER JOIN
    ret_PayrollCalculationDetails payrollCalculationDetails ON payrollCalculations.Guid = payrollCalculationDetails.[CalculationGuid]
WHERE 
    payrollCalculationCommands.[Hidden] = 0 AND
    payrollCalculationCommands.[Approved] = 1 AND
    payrollCalculations.[SalariedGuid] = @SalariedGuid AND
    payrollCalculations.[WorkingPeriodID] = @WorkingPeriodID AND
    payrollCalculations.[Year] = @Year
GROUP BY 
    payrollCalculationDetails.[ItemGuid]
)
SELECT
    paymentAllocation.[Guid],
    1 AS ItemType,
    paymentAllocation.[PaymentCommandType],
    '' AS ParameterName,
    paymentAllocationDetails.[Status],
    paymentAllocationDetails.[Amount],
    paymentAllocation.[Coffer],
    paymentTypes.[AffectOnSlipNature] AS [AffectOnSlip],
    paymentTypes.[AffectOnAmountNature] AS [AffectOnAmount],
    paymentTypes.[EffectiveOnSlip],
    paymentTypes.[BackPayable],
    0 AS [Reversible],
    lastAmounts.Amount AS LastAmount
FROM
    ret_PaymentAllocation AS paymentAllocation INNER JOIN
    ret_PaymentAllocationDetails AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] INNER JOIN
    ValidPaymentAllocations AS validPaymentAllocations ON 
    paymentAllocationDetails.[PaymentAllocationGuid] = validPaymentAllocations.[PaymentAllocationGuid] AND 
    paymentAllocationDetails.[Year] * 100 + paymentAllocationDetails.WorkingPeriodTitle = validPaymentAllocations.[WorkingPeriodKey] AND 
    paymentAllocationDetails.ID = validPaymentAllocations.MaxPaymentAllocationDetailID INNER JOIN
    ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] LEFT JOIN 
    LastAmounts lastAmounts ON lastAmounts.ItemGuid = paymentAllocation.Guid
WHERE
    ISNULL(paymentAllocation.Active,0) = 1 AND
    paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
    paymentAllocationDetails.[Status] = 1 /* Status.Save */
UNION ALL
SELECT
    payrollItems.[Guid],
    2 AS [ItemType],
    payrollItemPatterns.[PaymentCommandType],
    payrollItems.[ParameterName],
    0 AS [Status],
    0 AS [Amount],
    0 AS [Coffer],
    payrollItems.[Nature] AS [AffectOnSlip],
    0 AS [AffectOnAmount],
    payrollItems.[EffectiveOnSlip],
    payrollItems.[BackPayable],
    payrollItems.[Reversible],
    lastAmounts.Amount AS LastAmount
FROM
    ret_PayrollItemPatterns AS payrollItemPatterns INNER JOIN
    ret_PayrollItemPatternDetails AS PID ON payrollItemPatterns.[Guid] = PID.[PayrollItemPatternGuid] INNER JOIN
    ret_PayrollItems AS payrollItems ON PID.[PayrollItem] = payrollItems.ID LEFT JOIN 
    LastAmounts lastAmounts ON lastAmounts.ItemGuid = payrollItems.Guid
WHERE
    (payrollItemPatterns.[PaymentCommandType] = @PaymentCommandType AND payrollItemPatterns.[Guid] IN (@PayrollItemGuid))

Answer 1

Here's an explanation of the theory using a contrived example (because it's easier than trying to understand your exact situation and writing a query that uses your exact tables - you can hopefully translate this advice into what you want)

Imagine you have 3 tables: Person, WorkAddress, HomeAddress. We will ignore observations that the two different addresses should be in the same table with a discriminator for this contrived example

You want to pass a parameter to dictate what data is returned-work or home

You cannot do this:

SELECT * 
FROM
  Person p
  JOIN CASE WHEN @what = 'work' THEN WorkAddress ELSE HomeAddress END x
    ON p.Id = x.PersonId

Nice try, but you can't "return an entire table" from a case when

You have to join both, and then pick out in the select, what you want:

SELECT 
  p.*,
  CASE WHEN @what = 'work' THEN w.Building ELSE h.Building END as Building,
  CASE WHEN @what = 'work' THEN w.Street ELSE h.Street END as Street,
  ...
FROM
  Person p
  JOIN WorkAddress w ON p.Id = w.PersonId
  JOIN HomeAddress h ON p.Id = h.PersonId

If an address is missing, you need to LEFT JOIN it, otherwise the missing (work) address will cause the entire person and (available) home address to disappear from the results.

Larnu reasonably points out that LEFT JOINing will allow you to put the parameter into the ON clause, which may allow your DB to optimize it (never perform the join if the parameter comparison is false)

SELECT 
  p.*,
  COALESCE(w.Building, h.Building) as Building,
  COALESCE(w.Street, h.Street) as Street,
  ...
FROM
  Person p
  LEFT JOIN WorkAddress w ON p.Id = w.PersonId AND @what = 'work'
  LEFT JOIN HomeAddress h ON p.Id = h.PersonId AND @what = 'home'

Charlieface alo makes the reasonable point that you shouldn't do this at all; do an IF/ELSE if you're writing a stored procedure as it's the most reliable way of being sure about the query planning and performance optimizations that will take place. You can use a CTE for the parts common to both queries so it makes the whole thing easier to read