t-sql中用游标来读CTE

时间：2010-08-16 来源：左其盛

有同事问t-sql中能不能用游标来读CTE。
上google搜“CTE 游标”，结果都不得要领。msdn上说可以，但是没给出例子。
搜英文“CTE Cursor”，第一页的结果中就可以得出结论。第一个排名比较靠前的是一个凑乎的方案，把CTE的结果存入表变量，然后游标从表变量中读取数据（http://forums.aspfree.com/microsoft-sql-server-14/create-cursor-on-common-table-expression-134482.html）
declare @N int
declare @S int
declare @rectable table
(
N int,
S int
);

with MyCTE(N, S)
as
(
select 1,2
)
INSERT into @rectable (n,s)
Select* From MyCTE;
declare csr CURSOR for
SELECT * from @rectable
Open csr Fetch next from csr into @N, @S
WHILE @@fetch_status<>-1
begin
print @N
print @S --Test to see if anything prints (Nothing does)
Fetch next from csr into @N, @S
end
close csr
deallocate csr
第二个方案给出了可以编译通过的语句，直接用游标从CTE中读数据（http://www.developmentnow.com/g/113_2006_4_0_0_745653/Cursor-CTE-and-syntax-issue-Thanks-for-your-help.htm）：
declare @olnID int
declare @tmp table (olnID int, olnParentID int)
insert @tmp values (1,null)
insert @tmp values (2,1)
insert @tmp values (3,2); DECLARE crx CURSOR LOCAL FAST_FORWARD FOR
WITH oln_tree (olnID, olnParentID, P) AS
(
SELECT
oln.olnID , null , cast(str(oln.olnID) as varchar(max)) as P
FROM @tmp oln
WHERE olnParentID IS NULL UNION ALL SELECT
oln.olnID
, oln.olnParentID
,cast(P + str(oln.olnID) as varchar(max)) as P
FROM @tmp oln
JOIN oln_tree t on t.olnID = oln.olnParentID
)
SELECT olnID
FROM oln_tree
ORDER BY P DESC; OPEN crx FETCH NEXT FROM crx INTO @olnID
WHILE @@FETCH_STATUS = 0
BEGIN
select @olnID
--call sp EXECUTE dbo.spBAS_delOrderLines @olnID = @olnID
FETCH NEXT FROM crx INTO @olnID
END CLOSE crx
DEALLOCATE crx 第三个方案给出了通用的语法格式（http://smehrozalam.wordpress.com/2009/11/16/t-sql-using-cursor-with-common-table-expressions/）：
--declare a cursor above the CTE definitions
Declare myCursor Cursor Fast_Forward For
--declare CTEs
With CTE1 as
(
--CTE1 definitiion
)
,CTE2 as
(
--CTE2 definitiion
)
--select query as normal
Select ... From CTE2
--now open and use the cursor and don't forget to close and deallocate it in the end