在递归CTE而不是Cursor中以编程方式执行存储过程 [英] Execute stored procedure programmatically inside recursive CTE instead of Cursor
本文介绍了在递归CTE而不是Cursor中以编程方式执行存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想实现递归CTE而不是跟随游标,因为通过在游标内部以编程方式执行存储过程来插入数据需要花费大量时间,尤其是当表中的记录数量较多时。我已经尝试过使用CTE表达式来解决此问题,但是我无法用CTE生成相同的表达式,并且光标真的很烂,我完全想用CTE替换它,因此有什么办法可以缩短它。
I want to implement recursive CTE instead of following cursor because it takes lot of time to insert data by executing stored procedures programatecally inside cursor especially when there is larger number of records in table. I have tried CTE expression to solve this issue but i was failed to generate same with CTE and cursor really sucks and i totally want to replace it with CTE so is there any way to short out this. Advance thanks.
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
推荐答案
像这样吗?
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
这篇关于在递归CTE而不是Cursor中以编程方式执行存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文