Using a cursor with a CTE

common-table-expression, sql, sql-server

Solution

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

Problem

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

Original source