Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a cursor with a CTE

Tags:

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

like image 615
Pavan Kumar Avatar asked Jan 30 '13 10:01

Pavan Kumar


People also ask

How do I use CTE with cursor?

In order to use that CTE in a cursor, I had to define it right inside the “DECLARE Emp_Cursor CURSOR” statement. Some people make the mistake of trying to define their cursor right before the “DECLARE … CURSOR” statement. When you do this, you will get an error.

Is CTE faster than cursor?

There are cases you can provide better algorithm. From what I know SQL Server will always throw the data from the cursor to disk, this conforming may be that the CTE is faster in all simpler situations because it stays in memory, but it is an implementation failure, and that can change one day.

What is not allowed in CTE?

Cannot use with CTE"SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member. A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.

Is using CTE better than subquery?

CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.


Video Answer


1 Answers

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

like image 53
Damien_The_Unbeliever Avatar answered Sep 16 '22 14:09

Damien_The_Unbeliever