All geek questions in one place

Added new entry TSQL Cursor

I wrote a cursor

DECLARE CURSOR_SendMail CURSOR FAST_FORWARD FOR SELECT AlertId,AlertDetailsId,AlertDescription ,AlertTarget,ProjectDetailId,Subject FROM tblAlert WHERE AlertId > @MaxAlertID

Here @MaxAlertID is some id, so records over this id will be mailed.

I want to ask: If you select records one by one and send by mail, whether any new record inserted in the tblAlert table, or only the records that were available when the cursor was declared, will be taken into account.

eg. When the cursor is declared, max id is present in the table 1000 and @MaxAlertID is 0. Therefore, when I start sending mail for each record from 1 and above, and I reach some kind of record 517, and several records are added to tblAlert with 1001 and 1002 therefore 1001 1002 will be considered or only up to 1000 will be considered.

Need a clue as soon as possible, thanks!

+4
source share
3 answers

See the documentation for DECLARE CURSOR . There are options that you can specify to get the right behavior that you did not tell us about.

If you do not want to review new entries, try specifying STATIC or KEYSET . If you want to review new entries, specify DYNAMIC .

In fact, I can’t remember what the default behavior is - and it seems I can’t find it at the moment. If I need a specific behavior, I would always specify it, rather than relying on the default value.

+4
source

The default value is dynamic unless you change it.

+1
source

To quote:

DYNAMIC

Defines a cursor that reflects all data changes made for rows; its result is set when the cursor is scrolled. Data values, row order, and membership can change with each sample. The ABSOLUTE fetch option is not supported by dynamic cursors.

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

0
source

Source: https://habr.com/ru/post/1393313/

More articles:


All Articles