...but I personally dislike the construct which induces the bug. From the documentation we have the following which causes the problem:

DECLARE 
@x int,
@t varchar (255),
@r int

SELECT @x = 0
SELECT @x = id FROM sysobjects
WHERE
(id > @x) AND
(type = 'P')
ORDER BY id DESC
SELECT @r = @@ROWCOUNT

/*** COMMENT: Printing our resulting value of @x and @r ***/
SELECT @t = 'Resulting value of @x = ' + CONVERT(VARCHAR, @x)
PRINT @t
SELECT @t = 'Number of records in data set @r = ' + CONVERT(VARCHAR, @r)
PRINT @t
GO

I've marked in red what causes the difficulty. Specifically, what is happening seems to be that the variable is both used in the WHERE clause and is being assigned to. Technically, the software should take a flash of the @x value prior to running the query and use that pre-query value for the entire query.

What is likely happening, though, is that the value of @x is really just a pointer. So the WHERE clause is using a value of the @x variable that is changing as the query is trundling through the rows.

Anyhow, I dislike the construct because it is a bit confusing for the reason that it's not obvious you are (a) relying on the value of @x to be a constant in the WHERE clause while (b) assigning the value of the last row returned to the variable when completed. It is much better to (a) use seperate variables for the two distinct purposes; and (b) construct the query such that it only returns one row - the row that actually contains the required data.

Anyhow, register me as one that thinks that it is a trick in T-SQL that should be avoided - whether it works as advertized or not.