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

Parameters Expand / Collapse
Author
Message
Posted Thursday, November 3, 2011 8:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 17,940, Visits: 15,928
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1199927
Posted Thursday, November 3, 2011 9:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:20 PM
Points: 211, Visits: 299
Wouldn't the example given also error out if for some reason a Customer had more than one CustomerRefNo returned?
Post #1199971
Posted Thursday, November 3, 2011 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 6,086, Visits: 8,354
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
Posted Thursday, November 3, 2011 9:52 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: Yesterday @ 12:24 PM
Points: 3,384, Visits: 2,017
Nice question. I avoid linked servers whenever possible so this was new to me. Thanks!
Post #1200007
Posted Thursday, November 3, 2011 10:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:51 PM
Points: 2,010, Visits: 10,969
Great question. Learned SEVERAL things.

The more one learns, the more one realizes how little he knows...


Rob Schripsema
Accelitec, Inc
Post #1200028
Posted Thursday, November 3, 2011 10:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
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
Posted Thursday, November 3, 2011 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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
Posted Thursday, November 3, 2011 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Thursday, November 3, 2011 11:44 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Posted Thursday, November 3, 2011 2:53 PM


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 @ 12:36 PM
Points: 3,467, Visits: 1,827
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse