在对我的存储过程进行了许多更改之后,我认为它需要重新分解,主要是因为代码重复.如何克服这些重复：
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,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)),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)),day_flag = day_flag +'R2'
WHERE
trans_date = @TomorrowDate AND
emp_num = @empNum;
END
解决方法
您可以尝试以下操作以避免重复.基本上首先创建你的临时表(我已经猜到了数据类型),然后只使用这个表作为插入,因为你正在使用if它将是插入到其中的一个或另一个.
那么如果您从表中执行Select *,则不需要CTE,因此只需从表中直接调用即可.由于该表只包含一个选择或另一个的数据,并且字段名称相同等,我们可以只使用一个更新,如果是,则不再需要：
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,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,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)),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)),day_flag = day_flag +'R2'
WHERE trans_date = @TomorrowDate AND emp_num = @empNum;
