<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Stewart Campbell
»
Parameters
29 posts, Page 2 of 3
««
1
2
3
»»
Parameters
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Thursday, November 03, 2011 8:21 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 18,857,
Visits: 12,441
I skipped right over that Declare + Set and the fact that it was for 2005 and up since I had been doing that kind of set for some time in 2008. I guess that is lucky??
Nice question idea.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1199927
Robert.Hoffmann
Robert.Hoffmann
Posted Thursday, November 03, 2011 9:10 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, June 10, 2013 6:18 AM
Points: 202,
Visits: 236
Wouldn't the example given also error out if for some reason a Customer had more than one CustomerRefNo returned?
Post #1199971
Hugo Kornelis
Hugo Kornelis
Posted Thursday, November 03, 2011 9:46 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 5,296,
Visits: 7,237
Robert.Hoffman (11/3/2011)
Wouldn't the example given also error out if for some reason a Customer had more than one CustomerRefNo returned?
No. In that case, it would (more or less random (*)) select one of the RefNo values and return that one.
(*) To be precies, it would process them all, assigning them to the variable in turn, each time overwriting the previous assignment. Only the last assignment would stick, so the value returned would be from the row that happens to be processed last - which, in the absence of an ORDER BY, can be any row.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis
Post #1200002
KWymore
KWymore
Posted Thursday, November 03, 2011 9:52 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 6:13 AM
Points: 2,598,
Visits: 1,551
Nice question. I avoid linked servers whenever possible so this was new to me. Thanks!
Post #1200007
Rob Schripsema
Rob Schripsema
Posted Thursday, November 03, 2011 10:16 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 1,654,
Visits: 10,864
Great question. Learned SEVERAL things.
The more one learns, the more one realizes how little he knows...
Rob Schripsema
Accelitec, Inc
Post #1200028
Revenant
Revenant
Posted Thursday, November 03, 2011 10:27 AM
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 3,508,
Visits: 2,588
L' Eomot Inversé (11/3/2011)
Nice question. Pity it says 2005 onwards instead of 2008 onwards, as none of the answers match 2005 behaviour. ...
Exactly - it threw me for a loop...
Post #1200043
john.arnott
john.arnott
Posted Thursday, November 03, 2011 10:27 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491,
Visits: 3,010
Thank you for this mini-lesson in dynamic SQL. And don't fret too much over the minor glitches -- I know from experience that it's a lot harder than it looks to compose a QOD that no one finds anything to complain about.
Post #1200045
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Posted Thursday, November 03, 2011 10:27 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176,
Visits: 778
Good question - I don't work with linked servers often so this was a good one for me to reflect on - cheers!
Post #1200046
mtassin
mtassin
Posted Thursday, November 03, 2011 11:44 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 3,250,
Visits: 65,550
MarkusB (11/3/2011)
I agree with Philip, non of the given answers is correct.
The question explicitly says 2005 or higher, but the result on a 2005 server is different from a 2008 server.
I'm with these guys... I was staring at it and saying over and over assigning values at variable declaration was a 2008 feature that I love, and something I knew you couldn't do with 2005.
I took a random guess since they were all wrong with 2005
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1200109
Kenneth.Fisher
Kenneth.Fisher
Posted Thursday, November 03, 2011 2:53 PM
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 3,367,
Visits: 1,577
john.arnott (11/3/2011)
Thank you for this mini-lesson in dynamic SQL. And don't fret too much over the minor glitches -- I know from experience that it's a lot harder than it looks to compose a QOD that no one finds anything to complain about.
I agree, excellent question, I tend to use sp_executesql so I learned a lot about EXEC. I also agree with John about the minor glitches. I've put in a couple of QoDs and you have to expect complaints. If you read the discussions you will find that most QoDs have anything from a handful of complaints on up.
Kenneth
Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Link to my Blog Post -->
www.SQLStudies.com
Post #1200244
« Prev Topic
|
Next Topic »
29 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
