在 db_cursor1 中设置的变量不能用作 db_cursor2 中的 where 子句条件

Question

我无法下一个游标，也无法从db_cursor2的where子句中使用的db_cursor1设置变量。在声明db_cursor2时，它仅适用于声明时的@curLocation和@curTimeBandName设置。我希望"首先从db_cursor1获取到@curLocation，@curTimeBandName"将这些变量设置为新值，我可以使用这些新标准在db_cursor2上重新获取，但它不起作用。我讨厌在循环内移动db_cursor2的声明和释放。接下来有更好的方法，其中#1中设置的变量可以用作#2中的条件。

DECLARE
@Id INT,
@prevId INT = 0,
@prevEndDate DATETIME,
@curStartDate DATETIME,
@curEndDate DATETIME,
@curLocation NVARCHAR(100) = 'PHX',
@curTimeBandName NVARCHAR(50) = 'Long3'
SELECT @PrevEndDate = ISNULL(@startDate,'1900/01/01');
DECLARE db_cursor1 CURSOR 
SCROLL
FOR
SELECT DISTINCT [Location], TimeBandName
FROM #Temp  
ORDER BY Location, TimeBandName;
--  SELECT @curLocation = 'PHX', @curTimeBandName = 'Long3';
DECLARE db_cursor2 CURSOR 
SCROLL
FOR
SELECT Id, StartDate, EndDate
FROM #Temp
WHERE [Location] = @curLocation
AND TimeBandName = @curTimeBandName
ORDER BY Location, TimeBandName,  StartDate;
OPEN db_cursor1
OPEN db_cursor2
FETCH FIRST FROM db_cursor1 INTO @curLocation, @curTimeBandName
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM db_cursor2 INTO @Id, @curStartDate, @curEndDate
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@prevEndDate + 1 < @curStartDate)
BEGIN
UPDATE t
SET t.BlackoutStartDate = @PrevEndDate + 1,
t.BlackoutEndDate = @CurStartDate -1
FROM #Temp t
WHERE t.Id = @Id 
END
SELECT @prevId = @id, @prevEndDate = @curEndDate
FETCH NEXT FROM db_cursor2 INTO @Id, @curStartDate, @curEndDate
END
FETCH NEXT FROM db_cursor1 INTO @curLocation, @curTimeBandName
END
CLOSE db_cursor2;
DEALLOCATE db_cursor2;
CLOSE db_cursor1;
DEALLOCATE db_cursor1;

Answer 1

我找到了更好的方法。我一起转储了第二个光标。相反，我在临时表中添加了一个 ProcessStatus 字段来跟踪下一个未完成的记录，并添加了以下代码来跟踪要处理的下一个记录的女巫。

WHILE ...
BEGIN
SELECT TOP 1
@Id = Id,
@curStartDate = StartDate,
@curEndDate = @EndDate
FROM #Temp
WHERE [Location] = @curLocation
AND TimeBandName = @curTimeBandName
AND ProcessStatus = 0
ORDER BY Location, TimeBandName,  StartDate;
.... do work ...
UPDATE  t
SET ProcessStatus = 1
FROM #Temp t
WHERE Id = @Id;
END