使用带有CTE的光标

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

我仍然遇到错误

2 个解决方案

#1

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))

（但是，插入关于游标的常见注意事项几乎总是错误的工具。如果你能找到一种方法以基于集合的方式完成整个操作，它通常更可取，并且可能表现更好（或者至少更多）适合性能调整））

#2

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.

当声明为local @变量时，当变量超出范围时，游标将自动关闭并释放。

#1