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»»»

Incremental additions Expand / Collapse
Author
Message
Posted Friday, October 29, 2010 1:50 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
If the author wanted to trick us with the C-like pre-increment operator, this script would be more funny:

...

select @a = ++@b
select @a A

...

Post #1012850
Posted Friday, October 29, 2010 2:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
Hardy21 (10/29/2010)

Thanks for the link. Hence, @a =+ @b means @b value assign to @a so @a = -21.


Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1012855
Posted Friday, October 29, 2010 2:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 6,086, Visits: 8,354
da-zero (10/29/2010)
Hardy21 (10/29/2010)

Thanks for the link. Hence, @a =+ @b means @b value assign to @a so @a = -21.


Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.

Becuase there is no invalid syntax. Spaces are (mostly) ignored by SQL Server, so @a =+ @b is equivalent to @a=+@b, or to @a = +@b. The latter alternative is the most human-comprehensible. @a is assigned the result of applying the unqry + operator on @b. (And since the unary + operator is basically a no-op, you simply assign @a the value of @b).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1012856
Posted Friday, October 29, 2010 2:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
Hugo Kornelis (10/29/2010)
da-zero (10/29/2010)
Hardy21 (10/29/2010)

Thanks for the link. Hence, @a =+ @b means @b value assign to @a so @a = -21.


Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.

Becuase there is no invalid syntax. Spaces are (mostly) ignored by SQL Server, so @a =+ @b is equivalent to @a=+@b, or to @a = +@b. The latter alternative is the most human-comprehensible. @a is assigned the result of applying the unqry + operator on @b. (And since the unary + operator is basically a no-op, you simply assign @a the value of @b).


Great. Thanks for the explanation! Now it seems really simple.

(I was waiting until you would post on this thread and take all doubt away )

edit: your explanation comfirms my initial statement that the explanation of the questions' poster is incorrect, as it has nothing to do with pre-increment operators, but with misplacing the + so that SQL Server ignores the unary operator. Maybe the question would have been more difficult (or tricky) if it contained =-




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1012860
Posted Friday, October 29, 2010 2:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 6,086, Visits: 8,354
da-zero (10/29/2010)
edit: your explanation comfirms my initial statement that the explanation of the questions' poster is incorrect, as it has nothing to do with pre-increment operators, but with misplacing the + so that SQL Server ignores the unary operator. Maybe the question would have been more difficult (or tricky) if it contained =-

Agreed; the explanation is lacking.
Pre-increment operators in C++ are a double + (or a double -) before the operand. Like "a = ++b" - this adds 1 to b, then assigns the result to a. You can also use post-increment: "a = b++" first assigns the value of b to a, then adds one to the value of b. And if you use -- instead of ++, you get pre- and post-decrement.
SQL Server understands neither of these. The post-increment syntax should result in a syntax error. The pre-increment syntax runs, but is interpreted as a sequence of two unary plus operators (so "SET @a = ++@b;" is interpreted as "SET @a = +(+(@b));"). With decrement, the results are reversed - post-decrement runs (because "SET @a = @b--;" is interpreted as "SET @a = @b", since -- is the start of an inline comment). The pre-decrement does not run (also becuase -- is a comment token).

Based on the supplied answer options and the explanation, I think the question author tried to confuse us with the syntax for compound operators, that was introduced in SQL Server 2008. "SET @a += @b;" (note that the order of = and + is not the same as in the QotD!) will generate an error message in SQL Server 2005 and older; in SQL Server 2008 and later, it will add the value of @b to @a, i.e., it is equivalent to "SET @a = @a + @b;". One of the answers provided matches exactly what would be returned if the query had used += instead of =+.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1012866
Posted Friday, October 29, 2010 2:49 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
Sorry, I'm afraid I don't understand the point of the question.
Is it a trick to fool you into thinking the += compound operator? Or perhaps a typo when += was intended (but the answer doesn't support that theory)?
If not, it's just an assignation...

I stared at it for a while trying to work out what it was I was supposed to be noticing, or solving, or realising, but nothing came to me.

Maybe there's some SQL Server behaviour here that is unexpected from database experience only, whereas coming from a C# background I would think differently by default?

Post #1012867
Posted Friday, October 29, 2010 2:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:57 AM
Points: 2,587, Visits: 2,443
I think the question author tried to confuse us with the syntax for compound operators

I think the question author CONFUSED himself with the syntax for compound operators, as you can realize from author explanation: mismatch "compound operators" and "unary operator".
Post #1012870
Posted Friday, October 29, 2010 6:18 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 @ 3:03 AM
Points: 3,091, Visits: 981
Carlo Romagnano (10/29/2010)
I think the question author tried to confuse us with the syntax for compound operators

I think the question author CONFUSED himself with the syntax for compound operators, as you can realize from author explanation: mismatch "compound operators" and "unary operator".

Quite possibly, but it's only a few weeks since I helped a colleague who had stared at the exact same error for several hours without noticing the =+. Interestingly enough this was for string manipulation. One can't help but wonder what a unary plus means in the context of string manipulation. A unary minus for strings throws an error, thankfully!



Just because you're right doesn't mean everybody else is wrong.
Post #1012953
Posted Friday, October 29, 2010 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
Good question, and very good follow up discussion. Thanks to all.
Post #1013003
Posted Friday, October 29, 2010 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,212, Visits: 3,232
Amusingly, any sequence of unary operators before an integer (as long as two dashes together are avoided) seems to be valid in TSQL:

DECLARE @a int
SET @a = -+~-+~-+~-+~-+~-+~21
SELECT @a AS A

Playing with these sequences of operators made me realise that there is a way of mimicking the prefix increment and decrement operators in TSQL.

DECLARE @a int, @b int
SELECT @a = 100, @b = 0
SELECT @a AS a, @b AS b, -~@a AS [++a], ~-@b AS [--b], -~@a - ~-@b AS [++a - --b]

These seems to work "as expected" across the entire range of valid integers except for the single case of the increment operator on the minimum possible value of the integer (i.e. -2147483648 for the data type int). Also, it doesn't work properly with the tinyint type as negative values are not allowed with this type.

SELECT ~-N AS [--N], N, -~N AS [++N]
FROM (
SELECT -2147483647 UNION ALL
SELECT -100 UNION ALL
SELECT 0 UNION ALL
SELECT 100 UNION ALL
SELECT 2147483646
) TestData(N)
UNION ALL
SELECT ~-N AS [--N], N, NULL
FROM (
SELECT 2147483647
) TestData(N)

This contribution is for interest only. I'm certainly not suggesting that anyone actually makes use of these in production code!
Post #1013005
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse