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.

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! :)