SET the results

Question

Post reply

SET the results

Steve Jones - SSC Editor

SSC Guru

Points: 737620
More actions
January 1, 2018 at 11:53 pm

#77751

Comments posted to this topic are about the item SET the results

Viewing 15 posts - 1 through 14 (of 14 total)

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

manik_anu SSCrazy Points: 2397 More actions · Answer 1

new thing added in my database about "SET" statement

Manik
You cannot get to the top by sitting on your bottom.

Stewart "Arturius" Campbell SSC Guru Points: 72697 More actions · Answer 2

Nice one, thanks Steve.
Personal preference to use SELECT to assign values to variables.

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

Aaron N. Cutshall SSCrazy Eights Points: 9038 More actions · Answer 3

Oh silly me! I was so focused on the whole principle that order is not guaranteed without an ORDER BY clause that I didn’t notice the use of SET instead of SELECT. I’ll definitely need to pay attention to these more carefully!

Tricky one Steve, ya got me!

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 4

Good Question.
I was thinking to myself that not enough information was provided about the table structure to answer - since there is no guaranteed order, but we know how the engine behaves. I totally missed the SET statement.

Steve Jones - SSC Editor SSC Guru Points: 737620 More actions · Answer 5

This was one that surprised me as well. I didn't realize there was that much difference between SET/SELECT.

ManicStar SSCoach Points: 17992 More actions · Answer 6

Aaron N. Cutshall - Tuesday, January 2, 2018 4:46 AM
Oh silly me! I was so focused on the whole principle that order is not guaranteed without an ORDER BY clause that I didnâ€™t notice the use of SET instead of SELECT. Iâ€™ll definitely need to pay attention to these more carefully!Tricky one Steve, ya got me!

Me 2, I was going down the 'its a heap, it has no imposed order on it' path.

HappyGeek SSCoach Points: 18729 More actions · Answer 7

ManicStar - Tuesday, January 2, 2018 9:27 AM
Aaron N. Cutshall - Tuesday, January 2, 2018 4:46 AM
Oh silly me! I was so focused on the whole principle that order is not guaranteed without an ORDER BY clause that I didnâ€™t notice the use of SET instead of SELECT. Iâ€™ll definitely need to pay attention to these more carefully!Tricky one Steve, ya got me!
Me 2, I was going down the 'its a heap, it has no imposed order on it' path.

+1 on that!

...

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Revenant

SSC-Forever

Points: 42467

January 2, 2018 at 4:32 pm

#1974144

Simple and nice - thanks, Steve!

MMartin1 One Orange Chip Points: 27833 More actions · Answer 9

I'm glad I'm not the only one that missed the SET as I was thinking SELECT as well. Nice one Steve.

----------------------------------------------------

Carlo Romagnano SSC-Insane Points: 22737 More actions · Answer 10

The answer is a little confused!
The statement raises an error, because of incorrect syntax and not because of multiple values assignement. The following statements demonstrate this:

DECLARE @login VARCHAR(100);
SET @login = c.FirstName 
 FROM dbo.Contacts AS c
--Incorrect syntax near the keyword 'FROM'.
SET @login = (SELECT c.FirstName 
 FROM dbo.Contacts AS c)
--Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Stewart "Arturius" Campbell SSC Guru Points: 72697 More actions · Answer 11

Carlo Romagnano - Wednesday, January 3, 2018 1:29 AM
The answer is a little confused!
The statement raises an error, because of incorrect syntax and not because of multiple values assignement. The following statements demonstrate this:
DECLARE @login VARCHAR(100); SET @login = c.FirstName FROM dbo.Contacts AS c --Incorrect syntax near the keyword 'FROM'. SET @login = (SELECT c.FirstName FROM dbo.Contacts AS c) --Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Well spotted, Carlo

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

webrunner SSC-Dedicated Points: 31955 More actions · Answer 12

Thanks for this very instructive question!

Happy new year,
webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Marcia J SSCertifiable Points: 5649 More actions · Answer 13

Aaron N. Cutshall - Tuesday, January 2, 2018 4:46 AM
Oh silly me! I was so focused on the whole principle that order is not guaranteed without an ORDER BY clause that I didnâ€™t notice the use of SET instead of SELECT. Iâ€™ll definitely need to pay attention to these more carefully!Tricky one Steve, ya got me!

Same here. And I knew the right answer if I had just slowed down.

Marcia J SSCertifiable Points: 5649 More actions · Answer 14

At least, it's reassuring that I wasn't the only one that made that error.

SET the results

Cookies on SQLServerCentral