Cursor predicates aren't re-evaluated on-the-fly, are they?

[Breck_Carter]

Cursors 101: Please tell me that no matter what kind of cursor is being used, the result set doesn't change on-the-fly because of the second SET...

BEGIN

DECLARE @variable ...

SET @variable = [some value];

FOR ... SELECT ... WHERE [something] = @variable...

...

SET @variable = [some other value];

...

END FOR;

END;

---

Based on Ivan's reply (in one case, not this one, there is a problem) and Volker's comment (don't do that), I've decided straightforwardness is the better part of valor, and to use a second variable...

BEGIN

DECLARE @variable ...
   DECLARE @updated_variable ...

SET @variable         = [some value];
   SET @updated_variable = @variable;

FOR ... SELECT ... WHERE [something] = @variable...

...

SET @updated_variable = [some other value];

...

END FOR;

END;

Someone reading this code (me, a year from now) will see right away that something interesting is going on with @variable, it's not just a simple calculate-and-use thing. This will help, since the actual code is about 10x more complex.

And it eliminates the paranoia doubt.

[Breck_Carter]

No, I don't have any evidence of a problem, this is paranoia in its purest, most elemental form 🙂

[VolkerBarth]

I don't know (and I haven't tested) but I could imagine that for SENSITIVE cursors, it might make a difference since with such cursors, each row must be fetched one-by-one. Sorry if this leads to an increase in paranoia(:

[VolkerBarth]

@Breck: I agree that the edited version is much more comprehensible. - FWIW, when writing C++ code, we often declare local variables (even within short blocks) as "const" when they are meant to be just initialized and not changed afterwards. That's based on the same reasoning, methinks.

[Former Member · Accepted solution]

The value of variables do not vary within a statement except for one specific context, in the SET clause of an update statement that affects a variable. The following block returns 255 for @numrows.

create or replace variable @numrows int = 0;

update rowgenerator
set row_num = row_num, @numrows = @numrows + 1;

select @numrows;

Within the remainder of the execution plan, the value of the variable is fixed at open time. There are two cases here. Normally, the value of variable is known within optimization. In this case, the optimizer is free to use the value of the variable during predicate optimizations, while looking up predicate selectivities, or estimating costs. When a statement within a stored procedure is executed multiple times, it is eligible for plan caching. When plan caching is used, the optimizer builds a special plan that doesn't pay attention to the current values of variables. Instead, the value is retrieved when the plan starts to execute (in the "prepare" phase of the root). With plan caching, the optimizer can not use the variable values (they may change during subsequent uses of the statement).

Your paranoia may be infectious, Breck. I worried that the plan caching case might inadvertently pick up changes to the variable while the statement executes. My investigation this morning convinces me this is not the case. If it does happen, I consider it a bug and it should be fixed. Other than the UPDATE SET context, variables should take the value of the variable at the time the statement is opened.

---

Addendum from Breck: Just to drive Ivan's point home, this code doesn't set row_num = 0 for all the rows, just the first...

CREATE TABLE copy_RowGenerator ( row_num SMALLINT NOT NULL PRIMARY KEY );
INSERT copy_RowGenerator SELECT * FROM RowGenerator;
COMMIT;

create or replace variable @numrows int = 0;

update copy_rowgenerator
set row_num = @numrows, @numrows = @numrows + 1;

select * from copy_rowgenerator order by row_num;

row_num
0
1
2
3
4
5
...

Egad! Zounds! 🙂

[VolkerBarth · Accepted solution]

I'm glad that my suspicion (see my comment on Breck's question) has proved wrong. (Though I would refrain from using such dangerous constructs anyway...)

[Breck_Carter · Accepted solution]

@Volker: I suppose you're right, another variable doesn't cost anything and it cuts down on the night sweats. DECLARE @TinFoilHat, anyone? 🙂