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