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 4:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,915, Visits: 5,100
Hugo Kornelis (3/28/2011)
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 have found the use of variable value allocation and updating using SELECT to be very efficient and handy.

i.e., assigning or updating several variables using SET can only be assigned to 1 variable at a time, e.g.
SET @Var1 += x
SET @Var2 -= y
SET @Var3 = ''

while this could be effected in one instruction using the SELECT, e.g.
SELECT @Var1 += x, @Var2 -= y, @Var3 = ''




____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1084830
Posted Monday, March 28, 2011 4:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 9:26 AM
Points: 23, Visits: 14
michael.kaufmann (3/28/2011)SQL Server 2000 = v8.x; database mode 80 (aka compatibility level)
SQL Server 2005 = v9.x; database mode 90
SQL Server 2008 = v10.x; database mode 100


Thanks, just shows how detached from the actual database itself us programmers have become these days, I used to installed the DB's myself, but these days I don't get anywhere near that sort of stuff, we have people to do it for us (or do we do the programming for them!?).
Post #1084831
Posted Monday, March 28, 2011 6:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:24 PM
Points: 1,393, Visits: 478
Good easy 1 pt. Thank you
Post #1084863
Posted Monday, March 28, 2011 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 2,553, Visits: 3,800
stewartc-708166 (3/28/2011)
Hugo Kornelis (3/28/2011)
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 have found the use of variable value allocation and updating using SELECT to be very efficient and handy.

i.e., assigning or updating several variables using SET can only be assigned to 1 variable at a time, e.g.
SET @Var1 += x
SET @Var2 -= y
SET @Var3 = ''

while this could be effected in one instruction using the SELECT, e.g.
SELECT @Var1 += x, @Var2 -= y, @Var3 = ''




Thanks for the question. I have been coding on 2008 for awhile now and forgot that it would'nt work in prior versions. Regarding coding multiple assignments in one statement... I think it is easier to read multiple SET statements. But, to each her his/her own. I don't want to start a "religious war" over coding style. There's enough of that in the .NET forums.
Post #1084889
Posted Monday, March 28, 2011 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
simple one today
Post #1084910
Posted Monday, March 28, 2011 7:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
paul.james 64212 (3/28/2011)
OK, can somebody suggest why this returns "Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '='." on my 9.0.4207 SQL server?

declare @i tinyint
SELECT @i = 0
while @i < 255
SELECT @i+=1
SELECT @i

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.


because 9.0 is a sql server 2005 edition and += is not supported in this version
Post #1084914
Posted Monday, March 28, 2011 9:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 21,639, Visits: 15,309
thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1085018
Posted Monday, March 28, 2011 12:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:44 AM
Points: 998, Visits: 1,659
I just had an interesting occurance.

Server Version:10.0, 64-bit
Database Compatibility: 80

the snippet in the question copied verbatim into SSMS and worked. The result was 255.

I would have thought database compatibility mode would have prevented this...
Post #1085098
Posted Monday, March 28, 2011 12:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1085103
Posted Monday, March 28, 2011 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
venoym (3/28/2011)
I just had an interesting occurance.

Server Version:10.0, 64-bit
Database Compatibility: 80

the snippet in the question copied verbatim into SSMS and worked. The result was 255.

I would have thought database compatibility mode would have prevented this...


No Compatibility mode is not intended to disable new features. It's main goal is to keep existing code running. So the two spear points are:

1. If a new feature uses syntax that was not a reserved word in earlier versions, the feature is disabled in compat mode, so that old code that happens to use that word as an identifier continues to run.

2. If a feature has been deprecated in an earlier version already, it usually runs in compat mode only for one or more versions before being removed completely. This happened, for instance, to the ancient infixed outer join syntax (using =* and *=). Since SQL Server 2005, they are only available if you set the compatibility level to 80 or lower. And the implied sorting by a GROUP BY only works in SQL Server 2005 if you set compatibility to 60 or 65 - and since these levels are not supported in SQL Server 2008 and up, this "feature" is now gone for good.

There is no need (other than as described above) to disable new functionality when running in a lower compatibility level - unless you seriously excpect that people have written "SELECT @i+=1" in a SQL Server 2005 stored proc as a way to force an error, and will now become angry because their code suddenly no longer results in an error...



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1085108
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse