使用游标进行数据更新时
CREATE TABLE #tab
(
CategoryID BIGINT ,
StartTime DATETIME ,
EndTime DATETIME
)
INSERT #tab
( CategoryID ,
StartTime ,
EndTime
)
SELECT CategoryID ,
StartTime ,
EndTime
FROM V_OrderDetailInfo d
LEFT JOIN V_OrderInfo o ON d.OrderID = o.OrderID
AND @OrderStatus = 180
WHERE d.OrderID = @OrderID
AND ( IsReturnVenue = NULL
OR IsReturnVenue = 0
)
DECLARE curOrderDetail CURSOR
FOR
SELECT CategoryID ,
StartTime ,
EndTime
FROM #tab
OPEN curOrderDetail
FETCH NEXT FROM curOrderDetail INTO @CategoryID, @StartTime, @EndTime
WHILE @@Fetch_Status = 0
BEGIN
PRINT '数量调整'
PRINT @@FETCH_STATUS
UPDATE V_FreeVenueInfo
SET BookedNumber = BookedNumber - 1 ,
FreeNumber = FreeNumber + 1
WHERE CategoryID = @CategoryID
AND StartTime = @StartTime
AND EndTime = @EndTime
UPDATE V_OrderInfo
SET IsReturnVenue = 1
WHERE OrderID = @OrderID
FETCH NEXT FROM curOrderDetail INTO @CategoryID, @StartTime,
@EndTime
END
CLOSE curOrderDetail
DEALLOCATE curOrderDetail