SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Operators


Operators

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18949 Visits: 12426
SanDroid (3/28/2011)
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.
Thanks!


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
Abi Chapagai
Abi Chapagai
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 1127
Good Question, thanks.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26164 Visits: 12500
Christian Buettner-167247 (3/28/2011)
Tom.Thomson (3/27/2011)
One comment though: depending on settings of ARITHABORT and ANSI WARNINGS, it is quite possible that none of the answer options will result, because the batch is terminated before it reaches the select statement.

Why should the batch be terminated? I don't see any issues here.

If ARITHABORT is ON and ANSI WARNINGS off, arithmetic overlow causes the batch to be aborted. See the second sentence of the remarks section of this BoL page.
SELECT @i+=1 will cause arithmetic overflow on the last iteration, attempting to set a tinyint to 256.

Tom

UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4082 Visits: 2204
Tom.Thomson (3/29/2011)
If ARITHABORT is ON and ANSI WARNINGS off, arithmetic overlow causes the batch to be aborted. See the second sentence of the remarks section of this BoL page.
SELECT @i+=1 will cause arithmetic overflow on the last iteration, attempting to set a tinyint to 256.


Why would it do that, the WHILE clause:

while @i < 255



shouldn't run the SELECT statement to increment @i when @i is 255. So the last iteration is with @i=254 getting incremented to 255.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26164 Visits: 12500
UMG Developer (3/29/2011)
Tom.Thomson (3/29/2011)
If ARITHABORT is ON and ANSI WARNINGS off, arithmetic overlow causes the batch to be aborted. See the second sentence of the remarks section of this BoL page.
SELECT @i+=1 will cause arithmetic overflow on the last iteration, attempting to set a tinyint to 256.


Why would it do that, the WHILE clause:

while @i < 255



shouldn't run the SELECT statement to increment @i when @i is 255. So the last iteration is with @i=254 getting incremented to 255.


O mo chreach! I must have gone crazy to read < as <=. Blush

Tom

Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2232 Visits: 1549
i read somewhere, this is a new feature in sql server 2008. That's why i got point.

Nice question.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
zymos
zymos
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 263
Thank you for the good question, reviewing a feature that was introduced in SQL Server 2008 that did not exist in SQL Server 2005
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search