Assignment of a value to a variable

Question

Assignment of a value to a variable

John Mitchell-245523

SSC Guru

Points: 148809
More actions
January 30, 2020 at 12:00 am

#3712773

Comments posted to this topic are about the item Assignment of a value to a variable

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22600 More actions · Answer 1

That's correct: "an empty result set preserves the existing value of the variable", NO result set, NO assignment. In the case of SET or SELECT with a subquery (as shown below), it acts as an expression that always returns a value or NULL.
SELECT @j-2 = (SELECT j FROM #John WHERE 1=0);
SET @j-2 = (SELECT j FROM #John WHERE 1=0);

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Nuts! I wasn't paying attention.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ildjarn.is.dead Ten Centuries Points: 1159 More actions · Answer 3

Since answer 2 and 5 are the same, they can't be the correct answer. So that leads almost automatically to the right answer.

steveo250k SSC Veteran Points: 280 More actions · Answer 4

I was curious that 2 and 5 were the same. But that, in itself, doesn't make them wrong.

srienstr SSCrazy Points: 2435 More actions · Answer 5

It has happened once before that identical answers were correct. People who picked the "wrong" correct answer were later credited.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Yeah, if we screw it up, we try to make it right.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Thanks, everybody, for your feedback. I don't know how I managed to have the same answer twice, and it's six weeks now since I submitted the question. I probably copied and pasted it but then forgot to edit it, or something. Lucky I didn't omit the correct answer! And I managed to let a typo into the final option as well.

John

George Vobr SSCrazy Eights Points: 9916 More actions · Answer 8

Great question, thanks John. Some typo doesn't matter, and the same two answers don't. After all, one correct answer rules out the remaining... Thanks also to Carlo Romagnano for clarifying that the use of SET or SELECT with a subquery always returns a value or NULL.

George

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 9

Nice question, thanks John

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

MilesC Ten Centuries Points: 1078 More actions · Answer 10

MilesC

Ten Centuries

Points: 1078

February 3, 2020 at 9:26 pm

#3722757

Nice question, thx John!!