Take the 2-minute tour ×

I wrote a cursor

SELECT AlertId,AlertDetailsId,AlertDescription  
FROM    tblAlert   
WHERE AlertId > @MaxAlertID

Here @MaxAlertID is some id so that records above that id will be mailed.

What I want to ask is: While fetching records one-by-one and mailing them, will any new record inserted in tblAlert table also be considered or just the records that were available while declaring the cursor.

e.g. At the time of declaring cursor max id present in table is 1000 and @MaxAlertID is 0. So when I start sending mail for each record from 1 and above and I reach at some record 517 and a couple of records get added to tblAlert with 1001 and 1002, so those 1001,1002 will be considered or just upto 1000 will be considered.

Need clue ASAP,Thanks!

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Look at the documentation for DECLARE CURSOR. There are options you can specify so that you get the behaviour you want - which you haven't told us.

If you don't want to consider new records, consider specifying STATIC or KEYSET. If you do want to consider new records, specify DYNAMIC.

I can't actually remember what the default behaviour is - and can't seem to find it at the moment. If I needed specific behaviour, I'd always specify it rather than rely on the default.

share|improve this answer
A FAST_FORWARD cursor can end up as either static or dynamic –  Martin Smith Jan 27 '12 at 10:17
add comment

To quote from the documentation:



So adding DYNAMIC to your cursor definition will give you what you want. Or do you need the opposite? Then make a STATIC cursor

share|improve this answer
Thanks a lot man your answer is too perfect. –  MaxRecursion Jan 27 '12 at 8:23
add comment

The default is Dynamic, unless you have changed it.

share|improve this answer
add comment

Your Answer


