Hugo Kornelis (3/28/2011)
I got it correct.
I was surprised at first at the "SELECT @i+=1" syntax. I knew about the compount operators and have happily used "SET @i=1", but seeing never realised that it can equally well be used with SELECT.
I think setting variables with a SELECT statement like "SELECT @var1 = 1, @var2 =2" has always worked in SQL server.
I have a pre SQL 92 SQL command refference with examples and it is listed there.
It was also supported on MS SQL Server 7.
Yes, it has. For as long as I can remember (and that includes some vague recollection of SQL Server 4.2).
It just never occured to me that the += operator can also be used in a SELECT instead of only in a SET. My fault entirely - and I'm glad that this question showed me that this is possible as well. (Though I still prefer to use SET for setting variables).
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis