Stored Procedure: Reduce Code Duplication Using Temporary Tables

Question

After many changes to my stored procedure, I think it is necessary for repeated factoring, mainly due to 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

You can try the following to avoid duplication. First create your temporary table first (I guessed about the data types), and then use only this table for insertion, as you use if it will be one or the other that inserts into it.

Then you do not need CTE if you make Select * from the table, so just call directly from the table. Since the table will only have data from one selection or another, and the field names are the same, etc., we can just use one update for this and no longer need if:

 Create table #Residence (dayDate varchar(9), shiftName varchar(20), limit int, startTimeBefore time, endTimeBefore time, dayAdd int, name varchar(30), overtimeHours int, startTime time, endTime time) IF @transExist > 0 BEGIN IF @transType = 1 --INSERT BEGIN Insert into #Residence SELECT a.dayDate,a.shiftName,a.limit,b.startTimeBefore,b.endTimeBefore,b.dayAdd,b.name,b.overtimeHours,c.startTime,c.endTime 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; END ELSE BEGIN RETURN ; END END ELSE BEGIN IF @employeeExist > 0 BEGIN Insert into #Residence SELECT a.dayDate,a.shiftName,a.limit,b.startTimeBefore,b.endTimeBefore,b.dayAdd,b.name,b.overtimeHours,c.startTime,c.endTime 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; END ELSE BEGIN RETURN ; END END; 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::' +R.shiftName +'[ '+ R.name +' ]' FROM rr_overtime AS t1 INNER JOIN #Residence R ON t1.[trans_date] = R.[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; 

Answer 2

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 the union of all selected for both times. tables, but only extracts data from the correct one based on variables and uses it as a CTE to update. I also deleted the outer join because the table also participated in the inner join.

Although this can shorten the code, it is not always the best way to do something, because it can lead to the use of a more complex query plan, causing performance problems.

I also removed the correct (2, ...) functions from the time conversion, since the time conversion works without a leading zero, and the latter was only fixed at 00:00:00.

Answer 3

The code creating the temporary table looks the same, so I combined it in a CTE. The code in the update has slight differences that can be processed using the CASE statement. The CASE statement can be moved to the CTE if necessary.

So try:

 IF @transExist > 0 BEGIN IF @transType <> 1 RETURN ELSE SET @is_trans = 1 END ELSE BEGIN IF @employeeExist <= 0 RETURN ELSE SET @is_trans = 0 END ;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 #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; ) UPDATE t1 SET t1.over_time = CASE WHEN @Is_Trans = 1 THEN 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) ELSE 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) END, 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; 

Answer 4

try using dynamic query

In the insertion request it depends only on the table, depends on the condition, right? So use something like this

 DECLARE @FromTable NVARCHAR(250) IF @transExist > 0 BEGIN IF @transType = 1 BEGIN --INSERT SELECT @FromTable = '#ShiftTrans' SET @is_trans = 1; END ELSE BEGIN RETURN ; END END ELSE BEGIN IF @employeeExist > 0 BEGIN SELECT @FromTable = '#ShiftEmployees' SET @is_trans = 0; END ELSE BEGIN RETURN; END END; DECLARE @DynamicQuery NVARCHAR(MAX) = 'SELECT a.dayDate, a.shiftName, a.limit, b.startTimeBefore, b.endTimeBefore, b.dayAdd, b.name, b.overtimeHours, c.startTime, c.endTime INTO #Residence1 FROM ' +@FromTable +' 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' EXECUTE (@DynamicQuery) 

The SAMe method also creates a dynamic query for the update part ...

Thejus tv

Answer 5

The first part combines #Trans and #Employee with UNION ALL . But since you still need to distinguish between two, to make

 t1.over_time = t1.over_time --<------------- One is cumalative add and the other isn't + 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), 

for trans and

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

for Employee, I add an EmployeeType column to the CTE. In addition, some assumptions were made regarding the type of destination data in the rr_datatype table. If this is essentially TIME or DATETIME or some other option, you should use the DATEADD function. MSDN page here ..

And finally, the second UPDATE can also be combined into the first if you are only going to update the records participating in the first UPDATE and are not trying to update the entire table.

 ; WITH TransAndEmployee AS ( SELECT 'T' AS EmployeeType, dayDate, shiftName, limit FROM #ShiftTrans UNION ALL SELECT 'E' AS EmployeeType, dayDate, shiftName, limit FROM #ShiftEmployees ), PoundOvertime AS ( SELECT * FROM TransAndEmployee 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; ) UPDATE ot SET over_time = DATEADD(mi,@total_min, DATEADD(hh,overtimeHours,CASE WHEN EmployeeType = 'T' THEN over_time ELSE '0:00' END) ), day_flag = day_flag + 'R1', day_desc = 'R::' + CTE_Residence_Overtime_trans.shiftName + '[ ' + CTE_Residence_Overtime_trans.name + ' ]' FROM rr_overtime ot INNER JOIN PoundOvertime p ON ot.[trans_date] = p.[dayDate] WHERE ot.emp_num = @empNum UPDATE rr_overtime SET over_time = CAST('00:00:00' As Time), -- You can probably do over_time = '00:00' if over_time is TIME day_flag = day_flag +'R2' WHERE trans_date = @TomorrowDate AND emp_num = @empNum; 

Another assumption I made ... below seemed a bit odd to me. I assumed that you were trying to add “minutes” ... however this line of manipulation seems to add it to the clock. I “fixed” this ... let me know if this was a bad guess.

 + CAST(RIGHT('0'+ CAST(@total_min as varchar(2)),2) +':00:00' As Time),