有同事問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
