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.