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...


DECLARE @variable ...

SET @variable = [some value];

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


SET @variable = [some other value];




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...


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

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

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


SET @updated_variable = [some other value];




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.

asked 29 Oct '10, 10:09

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 06 Aug '11, 17:25

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

(29 Oct '10, 10:14) Breck Carter

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(:

(29 Oct '10, 10:45) Volker Barth

@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.

(30 Oct '10, 21:45) Volker Barth

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...

INSERT copy_RowGenerator SELECT * FROM RowGenerator;

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;


Egad! Zounds! :)

permanent link

answered 29 Oct '10, 15:12

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
accept rate: 39%

edited 29 Oct '10, 16:16

Breck%20Carter's gravatar image

Breck Carter

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

(29 Oct '10, 15:41) Volker Barth

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

(29 Oct '10, 16:19) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Oct '10, 10:09

question was seen: 724 times

last updated: 06 Aug '11, 17:25