SQL 使用带有 CTE 的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14601407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a cursor with a CTE
提问by Pavan Kumar
I need a cursor for the below query so I can loop through to fetch/update/insert some other data. Can somebody help me with this?
我需要一个用于以下查询的游标，以便我可以循环访问以获取/更新/插入一些其他数据。有人可以帮我解决这个问题吗？
DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
SELECT @FROMDATE = Getdate()
SELECT @TODATE = Getdate() + 7
;WITH DATEINFO(DATES)
AS (SELECT @FROMDATE
UNION ALL
SELECT DATES + 1
FROM DATEINFO
WHERE DATES < @TODATE)
SELECT *
FROM DATEINFO
OPTION (MAXRECURSION 0)
I tried so many ways, but didn't find any that worked.
我尝试了很多方法，但没有找到任何有效的方法。
I'm Using
我正在使用
declare @adate datetime
DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
select @FROMDATE=getdate()
select @TODATE =getdate()+7
declare @weekdates cursor for
WITH DATEINFO(DATES) AS (SELECT @FROMDATE UNION ALL SELECT DATES + 1 FROM DATEINFO WHERE DATES < @TODATE)
SELECT * FROM DATEINFO OPTION (MAXRECURSION 0)
open @weekdates
fetch next from @weekdates into @adate
while @@fetch_status=0
begin
print 'success'
fetch next from @weekdates into @adate
end
close @weekdates
deallocate @weekdates
Still I'm getting errors
我仍然收到错误
回答by Damien_The_Unbeliever
Just put it in before the common table expression:
把它放在公共表表达式之前：
DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
select @FROMDATE=getdate()
select @TODATE =getdate()+7
declare boris cursor for
WITH DATEINFO(DATES)
AS (SELECT @FROMDATE
UNION ALL
SELECT DATES + 1
FROM DATEINFO
WHERE DATES < @TODATE)
SELECT *
FROM DATEINFO
OPTION (MAXRECURSION 0)
(However, insert usual cautions about cursors almost always being the wrong tool for the job. If you can find a way to do the whole operation in a set based manner, it's usually preferable, and likely to perform better (or at least be more amenable to performance tuning))
（然而，插入关于游标几乎总是错误的工作工具的通常警告。如果你能找到一种方法以基于集合的方式完成整个操作，通常是可取的，并且可能表现得更好（或至少是更多适合性能调优))
回答by Martin Smith
It is fine to use @in a cursor name but the syntax you are using is wrong.
可以@在游标名称中使用，但您使用的语法是错误的。
DECLARE @adate DATETIME
DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
SELECT @FROMDATE = getdate()
SELECT @TODATE = getdate() + 7
DECLARE @weekdates CURSOR;
SET @weekdates = CURSOR FOR
WITH DATEINFO(DATES)
AS (SELECT @FROMDATE
UNION ALL
SELECT DATES + 1
FROM DATEINFO
WHERE DATES < @TODATE)
SELECT *
FROM DATEINFO
OPTION (MAXRECURSION 0)
OPEN @weekdates
FETCH next FROM @weekdates INTO @adate
WHILE @@fetch_status = 0
BEGIN
PRINT 'success'
FETCH next FROM @weekdates INTO @adate
END
When declared as a local @variable the cursor is automatically closed and deallocated when the variable goes out of scope.
当声明为局部@变量时，游标会在变量超出范围时自动关闭并释放。
