将光标与CTE一起使用 [英] Using a cursor with a CTE
本文介绍了将光标与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.
我正在使用
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
仍然出现错误
推荐答案
只需将其放在通用表表达式之前:
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))
这篇关于将光标与CTE一起使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文