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


Operators


Operators

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16701 Visits: 7413
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”
paul.james 64212
paul.james 64212
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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!?).
Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 513
Good easy 1 pt. Thank you
OCTom
OCTom
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: 4097 Visits: 4152
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. w00t
sharath.chalamgari
sharath.chalamgari
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 798
simple one today
sharath.chalamgari
sharath.chalamgari
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 798
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64923 Visits: 18570
thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

venoym
venoym
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 2082
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...
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2378 Visits: 1046
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.
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: 18483 Visits: 12426
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
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