Using a cursor with a CTE
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.
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))