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.

Advertisements
Posted in T-SQL. Tags: , , . 5 Comments »

5 Responses to “T-SQL: Using cursor with Common Table Expressions”

  1. DotNetShoutout Says:

    T-SQL: Using cursor with Common Table Expressions « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Israel Says:

    Thank you very much!!

  3. Kevin Says:

    Aha! Thanks for the tip 🙂

  4. Fragkiska Says:

    Thank you so much !!!

  5. Ayesha Farid Says:

    Nice Tip.. 🙂
    Also very nice way to explain….easy to understand


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: