Stored procedure: reduce code duplication using temp tables

Question

After many changes to my stored procedure, I think it needs to re-factoring , mainly because of code duplication. How to overcome these duplications:

IF @transExist > 0 BEGIN
    IF @transType = 1 BEGIN --INSERT
        SELECT
            a.dayDate,
            a.shiftName,
            a.limit,
            b.startTimeBefore,
            b.endTimeBefore,
            b.dayAdd,
            b.name,
            b.overtimeHours,
            c.startTime,
            c.endTime
        INTO 
            #Residence1
        FROM 
            #ShiftTrans a 
            RIGHT OUTER JOIN #ResidenceOvertime b
                ON a.dayDate = b.dayDate
            INNER JOIN ShiftDetails c
                ON c.shiftId = a.shiftId AND 
                c.shiftTypeId = b.shiftTypeId;

        SET @is_trans = 1;
    END ELSE BEGIN
        RETURN ;
    END
END ELSE BEGIN
    IF @employeeExist > 0 BEGIN
        SELECT
            a.dayDate,
            a.shiftName,
            a.limit,
            b.startTimeBefore,
            b.endTimeBefore,
            b.dayAdd,
            b.name,
            b.overtimeHours,
            c.startTime,
            c.endTime
        INTO 
            #Residence2
        FROM 
            #ShiftEmployees a 
            RIGHT OUTER JOIN #ResidenceOvertime b
                ON a.dayDate = b.dayDate
            INNER JOIN ShiftDetails c
                ON c.shiftId = a.shiftId AND 
                c.shiftTypeId = b.shiftTypeId;

            SET @is_trans = 0;
    END ELSE BEGIN
        RETURN;
    END
END;

IF @is_trans = 1 BEGIN
    WITH CTE_Residence_Overtime_trans AS (
        SELECT * FROM #Residence1
    )

    UPDATE t1
    SET 
        t1.over_time = t1.over_time 
          + CAST(RIGHT('0'+ CAST(overtimeHours as varchar(2)),2)
             +':00:00' As Time) 
          + CAST(RIGHT('0'+ CAST(@total_min  as varchar(2)),2)
             +':00:00' As Time),
        t1.day_flag = t1.day_flag + 'R1',
        t1.day_desc = 'R::' 
          + CTE_Residence_Overtime_trans.shiftName +'[ '
          + CTE_Residence_Overtime_trans.name +' ]'
    FROM 
        rr_overtime AS t1
        INNER JOIN CTE_Residence_Overtime_trans 
            ON t1.[trans_date] = CTE_Residence_Overtime_trans.[dayDate]
    WHERE 
        t1.emp_num = @empNum;

    UPDATE rr_overtime 
    SET 
        over_time = CAST(RIGHT('0'+ CAST(0 as varchar(2)),2)+':00:00' As Time),
        day_flag = day_flag +'R2'
    WHERE 
        trans_date = @TomorrowDate AND 
        emp_num = @empNum;

END ELSE BEGIN
    WITH CTE_Residence_Overtime AS (
        SELECT * FROM #Residence2
    )

    UPDATE t1
    SET 
        t1.over_time = CAST(RIGHT('0'+ CAST(overtimeHours as varchar(2)), 2) 
          +':00:00' As Time)
        + CAST(RIGHT('0'+ CAST(@total_min  as varchar(2)),2)+':00:00' As Time),
        t1.day_flag = t1.day_flag + 'R1',
        t1.day_desc = 'R::' 
          + CTE_Residence_Overtime.shiftName +'[ '
          + CTE_Residence_Overtime.name +' ]'
    FROM 
        rr_overtime AS t1
        INNER JOIN CTE_Residence_Overtime 
            ON t1.[trans_date] = CTE_Residence_Overtime.[dayDate]
    WHERE 
        t1.emp_num = @empNum ;

    UPDATE rr_overtime 
    SET 
        over_time = CAST(RIGHT('0'+ CAST(0 as varchar(2)),2)+':00:00' As Time),
        day_flag = day_flag +'R2'
    WHERE 
        trans_date = @TomorrowDate AND 
        emp_num = @empNum;

END 

Answer 1

Looking at the code, it looks like this should work:

WITH CTE_Residence_Overtime_trans AS (
    SELECT
        a.dayDate,
        a.shiftName,
        a.limit,
        b.startTimeBefore,
        b.endTimeBefore,
        b.dayAdd,
        b.name,
        b.overtimeHours,
        c.startTime,
        c.endTime
    FROM
        ( 
            select dayDate, shiftName, limit
            from #ShiftTrans
            where (@transExist > 0 and @transType = 1)
        union all
            select dayDate, shiftName, limit 
            from #ShiftEmployees
            where (not (@transExist>0 and @transType=1)) and @employeeExist>0
        ) a
    JOIN #ResidenceOvertime b
        ON a.dayDate = b.dayDate
    JOIN ShiftDetails c
        ON c.shiftId = a.shiftId AND 
        c.shiftTypeId = b.shiftTypeId
)

UPDATE t1
SET 
    t1.over_time = t1.over_time 
      + CAST(CAST(overtimeHours as varchar(2))+':00:00' As Time) 
      + CAST(CAST(@total_min    as varchar(2))+':00:00' As Time),
    t1.day_flag = t1.day_flag + 'R1',
    t1.day_desc = 'R::' + CTE.shiftName +'[ ' + CTE.name +' ]'
FROM 
    rr_overtime AS t1
    INNER JOIN CTE_Residence_Overtime_trans CTE
        ON t1.[trans_date] = CTE.[dayDate]
WHERE 
    t1.emp_num = @empNum;

UPDATE rr_overtime 
SET 
    over_time = CAST('00:00:00' As Time),
    day_flag = day_flag +'R2'
WHERE 
    trans_date = @TomorrowDate AND 
    emp_num = @empNum;

This makes an union all select to both of the temp. tables, but only fetches data from the correct one based on the variables, and uses that as the CTE for the update. I also removed the outer join because the table was also involved in an inner join.

Although this can shorten the code, it is not always the best way to do things, because it might cause more complex query plan to be used causing performance issues.

I also removed the right(2,...) functions from time conversion, since time conversion works without leading zero too, and the last one was just fixed 00:00:00.