编程执行递归CTE来代替光标内部存储过程(Execute stored procedure prog

我想实现的，而不是跟随光标递归CTE，因为它需要很多的时候通过programatecally执行内部游标存储过程尤其是当有较大的表中的记录数插入数据。 我曾尝试CTE表达式来解决这个问题，但我没能产生同样具有CTE和光标真的很烂，我完全想CTE来代替它，有没有什么办法来短路这一点。 先谢谢了。

    Declare @Emp_Id As Numeric    
    Declare CurEmpWeekOff cursor for        
    Select Emp_Id From tblEmployee Where Cmp_Id = @Cmp_Id And Branch_Id = @Branch_Id And Emp_WeekOff_Type = 1    

    open CurEmpWeekOff        
    Fetch next From CurEmpWeekOff Into @Emp_Id    
    while @@Fetch_Status = 0        
    Begin        
  If @Is_Sunday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 0, '', @Login_Id, 0    
  If @Is_Monday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 1, '', @Login_Id, 0    
  If @Is_Tuesday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 2, '', @Login_Id, 0    
  If @Is_Wednesday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 3, '', @Login_Id, 0    
  If @Is_Thursday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 4, '', @Login_Id, 0    
  If @Is_Friday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 5, '', @Login_Id, 0    
  If @Is_Saturday = 1    
   Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, 6, '', @Login_Id, 0    

  Fetch next From CurEmpWeekOff Into @Emp_Id    
    End        
    Close CurEmpWeekOff        
    Deallocate CurEmpWeekOff            
   End  

Answer 1:

像这样的东西吗？

Declare @Emp_Id As Numeric
DECLARE @TempID INT
DECLARE @DayOfWeek INT

If @Is_Sunday = 1    
  SET @DayOfWeek = 1
  If @Is_Monday = 1    
   SET @DayOfWeek = 2

... INSERT REST HERE
  If @Is_Saturday = 1    
   SET @DayOfWeek = 6 


DECLARE #TempEmp TABLE
(
   ID INT IDENTITY,
   EmpID INT
)

INSERT INTO #TempEmp
(
   EmpID
)
Select Emp_Id From tblEmployee Where Cmp_Id = @Cmp_Id And Branch_Id = @Branch_Id And Emp_WeekOff_Type = 1


WHILE EXISTS (SELECT 1 FROM #TempEmp)
BEGIN

    SELECT TOP 1 @TempID = ID, @Emp_ID = EmpID FROM #TempEmp

    Exec [prcEmployee_WeekoffInsert] @Cmp_Id, @Branch_Id, @Emp_Id, @From_Date, @DayOFWeek, '',      @Login_Id, 0 

    DELETE FROM #TempEmp WHERE ID = @TempId
END

文章来源: Execute stored procedure programmatically inside recursive CTE instead of Cursor