如何避免光标........................... [英] How to Avoid cursor...........................

问题描述

-- i hava one temp table (#tempTbl)
DECLARE @Sno INT
DECLARE @Id INT
DECLARE @TotalMinutes AS INTEGER=0
DECLARE @OTimeMinutes AS INTEGER=0
DECLARE @RUMinutes INTEGER=0

DECLARE sumcal CURSOR GLOBAL SCROLL STATIC OPTIMISTIC FOR SELECT Id,Sno FROM #tempTbl
OPEN sumcal
FETCH FIRST FROM sumcal INTO @Id,@Sno
WHILE ( @@FETCH_STATUS = 0 )
BEGIN 
SELECT @RUMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno
SELECT @TotalMinutes = SUM(SUAM) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno
IF(@TotalMinutes>2400)
BEGIN
if (SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OTM>0)>0
BEGIN
SELECT @OTimeMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno SET @RUMinutes = 0
END
ELSE
BEGIN
SET @OTimeMinutes = (@TotalMinutes-2400)
SET @RUMinutes = (@RUMinutes - @OTimeMinutes)
END
END
ELSE
BEGIN
SET @OTimeMinutes = 0
END
UPDATE #tempTbl SET OTM = @OTimeMinutes, RSUM = @RUMinutes WHERE SNo=@Sno
SET @OTimeMinutes = 0
SET @TotalMinutes = 0
SET @RUMinutes = 0
FETCH NEXT FROM sumcal INTO @Id,@Sno
END
CLOSE sumcal
DEALLOCATE sumcal

#tempTbl table :
SNo	Id	SUAM	OTM	RSUM
1	254	480	0	0
2	254	480	0	0
3	254	480	0	0
4	254	480	0	0
5	254	480	0	0
6	254	480	0	0
7	277	480	0	0
8	277	480	0	0
9	277	480	0	0
10	277	480	0	0

output

SNo	Id	SUAM	OTM	RSUM
1	254	480	0	480
2	254	480	0	480
3	254	480	0	480
4	254	480	0	480
5	254	480	0	480
6	254	480	480	0
7	277	480	0	480
8	277	480	0	480
9	277	480	0	480
10	277	480	0	480

推荐答案

我找到了：将光标更改为基于查询/设置的查询/任何人 [ ^ ]





UPS！

有人删除上面的帖子。

报告在这里：http://www.codeproject.com/Messages/4661621/Is-it -possible-to-restore-deleted-QA-item-with-ans.aspx [ ^ ]

[/ EDIT]

I found it: change cursor to query/set based query/anyone[^]


UPS!
Someone delete above thread.
Reported here: http://www.codeproject.com/Messages/4661621/Is-it-possible-to-restore-deleted-QA-item-with-ans.aspx[^]
[/EDIT]

这篇关于如何避免光标...........................的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！