T-SQL: Using cursor with Common Table Expressions

Cursors aren’t a bad choice for certain scenarios. Few days back, I was writing a stored procedure that was to be scheduled as a SQL agent job. I needed to send emails for each row of a result set and hence concluded that a Fast Forward cursor (Read Only, Forward only cursor) would be a nice choice. I created several CTEs (common table expressions) to reach my final result set and declared a cursor for my final select statement like this:

--declare some CTEs
;With CTE1 as
(
   --CTE1 definitiion
)
,CTE2 as
(
   --CTE2 definitiion
)

--declare a cursor for final select statement
Declare myCursor Cursor Fast_Forward For
   --select query
   Select ... From CTE2

But I was greeted with the following error:

Incorrect syntax near the keyword 'Declare'.


Ok, so my expected syntax is incorrect. After a quick google, I found that the declare cursor statement needs to be on the top of CTE declarations. So, here’s the correct syntax to define cursor with CTEs:

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

Cool!! That was a nice learning.