Menu
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
615
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.
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.
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.
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.
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))
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
