Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Operators Expand / Collapse
Author
Message
Posted Monday, March 28, 2011 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 6,048, Visits: 8,329
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
Post #1085113
Posted Monday, March 28, 2011 3:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:36 PM
Points: 1,786, Visits: 1,116
Good Question, thanks.
Post #1085223
Posted Tuesday, March 29, 2011 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
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
Post #1085502
Posted Tuesday, March 29, 2011 1:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.

Post #1085836
Posted Tuesday, March 29, 2011 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
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 <=.


Tom
Post #1085845
Posted Friday, April 1, 2011 4:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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

Post #1087344
Posted Friday, March 2, 2012 4:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Thank you for the good question, reviewing a feature that was introduced in SQL Server 2008 that did not exist in SQL Server 2005
Post #1261078
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse