[转]SQL Server中用While循环替代游标(Cursor)的解决方案

create table Orders(OrderID int,CostValue decimal(18,2) );with cte_temp
as
(
select 1 as OrderID
union all
select OrderID+1 from cte_temp where OrderID<10000
)insert into Orders(OrderID)
select OrderID from cte_temp option (maxrecursion 32767);

--游标
DECLARE @OrderID intDECLARE cursor_CostValue CURSOR FOR SELECT OrderID FROM Orders
OPEN cursor_CostValue
FETCH NEXT FROM cursor_CostValue INTO @OrderID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Orders SET CostValue = OrderID+100 WHERE OrderID = @OrderID
FETCH NEXT FROM cursor_CostValue INTO @OrderID
END
CLOSE cursor_CostValue
DEALLOCATE cursor_CostValue

DECLARE @RowID int-- 获取待处理的数据记录到临时表
-- 字段说明：RowID：记录行号 / DealFlg：行处理标识
SELECT RowID = IDENTITY(INT , 1, 1),DealFlg=0,OrderID,CostValue = 0
INTO #Tmp
FROM Orders
SELECT @RowID = MIN(RowID) FROM #Tmp WHERE DealFlg = 0
-- 若最小行号不为空（有需要处理的数据）
WHILE @RowID IS NOT NULL
BEGIN
UPDATE #Tmp SET DealFlg = 1,CostValue=OrderID+100 WHERE RowID = @RowID SELECT @RowID = MIN(RowID) FROM #Tmp WHERE DealFlg = 0
END
update O set O.CostValue=T.CostValue
from Orders O
inner join #Tmp T on O.OrderID=T.OrderID

DECLARE @OrderID INT
--表中OrderID最小的值
SELECT @OrderID = MIN(OrderID) FROM Orders where CostValue is null
WHILE @OrderID IS NOT NULL
BEGIN
UPDATE Orders SET CostValue = OrderID+100 WHERE OrderID = @OrderID
SELECT @OrderID = MIN(OrderID) FROM Orders where CostValue is null
END