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


cast, convert and float!


cast, convert and float!

Author
Message
VM-723206
VM-723206
SSC Eights!
SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)

Group: General Forum Members
Points: 996 Visits: 267
Comments posted to this topic are about the item cast, convert and float!
azz
azz
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 85
Hi, the question has a misprint, probably intended:
select @d = cast(convert(varchar(2),convert(varchar(3),@c)) as float) * 0.5
select @d
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2903 Visits: 711
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?
JacekO
JacekO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 616
Scary part is over 30% (at the time of this post) got it wrong. Many QoDs are quite tricky but this one is very basic and really not SQL specific (data type conversion or casting is an ABC of any programming language).
Looks like a concept of code review or inspection is missing way too often...

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Chris Harshman
Chris Harshman
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10428 Visits: 4631
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions ;-)
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 169
Mark Horninger (8/20/2009)
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?

I was on that in another discussion. It doesn't say "deprecated", it does say "we recommend you don't use it", and no one knew why. Is SQL Server 7.0 or earlier around anywhere so we could see whether at any time it was announced deprecated? Or did we already do that?

I put the wrong answer, 10.5, I missed that the calculation result was stored back to the int variable(!)

Two thoughts on these exercises generally:

1. Often you look at the puzzle code and go "But you wouldn't do it that way!" But 1a, that isn't the point. And, 1b, the way "you" would do it may be different from the way "that guy who wrote some stuff for us till we found out he was raised by squirrels from birth and not a real MSCE at all" would do it. Some people do write squirrelly code.

A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!

2. If you only go in on the questions you think you know, and skip the harder ones, it skews the scoreboard. But 2a, who cares. ;-)
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2903 Visits: 711
rja.carnegie (8/20/2009)
Mark Horninger (8/20/2009)
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?

I was on that in another discussion. It doesn't say "deprecated", it does say "we recommend you don't use it", and no one knew why. Is SQL Server 7.0 or earlier around anywhere so we could see whether at any time it was announced deprecated? Or did we already do that?

I put the wrong answer, 10.5, I missed that the calculation result was stored back to the int variable(!)

Two thoughts on these exercises generally:

1. Often you look at the puzzle code and go "But you wouldn't do it that way!" But 1a, that isn't the point. And, 1b, the way "you" would do it may be different from the way "that guy who wrote some stuff for us till we found out he was raised by squirrels from birth and not a real MSCE at all" would do it. Some people do write squirrelly code.

A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!

2. If you only go in on the questions you think you know, and skip the harder ones, it skews the scoreboard. But 2a, who cares. ;-)



HEre's what I found - Set Vs. Select

http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

http://vyaskn.tripod.com/differences_between_set_and_select.htm
JacekO
JacekO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 616
A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!


Maybe both of you are quite OK and the missing part is good comments in the code...

Sometimes I write some SQL code I can not figure out myself few weeks later if I do not comment it correctly.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

webrunner
webrunner
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7515 Visits: 3993
I just plain got it wrong because of the int @c, after focusing too much on confirming the 21 * 0.5 calculation. Good question in that it highlights rookie mistakes I shouldn't be making....

Thanks,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
ronmoses
ronmoses
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1658 Visits: 1011
I also missed the assignment back to the int variable. Oops. It would have been an obvious answer if only I'd paid better attention.

SELECTing into a variable is not being deprecated. But there are a few things to know when deciding whether to use SET or SELECT:

1) SET is the ANSI standard for assigning variable values. So if you care about that, there's your answer.

2) When assigning a value from a query, SET will raise an error if multiple rows are returned. SELECT will simply assign the last returned value and go about its merry way.

3) If the query returns zero rows, SET will set the variable to NULL. SELECT will leave the variable set to its current value.

4) SELECT can assign values to multiple variables simultaneously. So if you have three variables that each need a value from a given table, you'd have to query the table three times using SET, and only once with SELECT. Obviously this would have performance benefits in certain scenarios.

The conventional wisdom, as far as my research indicates, is that SELECT is preferable when you need to set multiple variables from a single query. SET is preferable when setting a single variable, or if ANSI-compliance is a requirement.

Ron Moses

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

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