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 Wednesday, November 2, 2011 10:14 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: Yesterday @ 11:15 AM
Points: 3,964, Visits: 5,204
Comments posted to this topic are about the item Parameters

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1199715
Posted Thursday, November 3, 2011 2:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:00 AM
Points: 1,011, Visits: 863
When running this in SQL2005 I get the following response (after answering the question)

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@CustAccount".

You can not set the value of a variable at the same time as declaring it until SQL 2008.
Post #1199752
Posted Thursday, November 3, 2011 2:42 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,432, Visits: 4,171
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.


Markus Bohse
Post #1199756
Posted Thursday, November 3, 2011 2:55 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 @ 11:15 AM
Points: 3,964, Visits: 5,204
I see the possible cause...
I appear to have forgotten to shift the variable into it's own statement (i.e. SET @CustAccount = '50854258878') to cater for SQL2005 (which I no longer have installed on my machine)
If you adjust the script in this vein, the result will be successful....

However, that despite, the question relates to how parameterisation and placeholders work


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1199759
Posted Thursday, November 3, 2011 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
A nice question about a little known feature - but you have to overllook the errors.

I'll admit that I did not see the declare + set statement, as I was not focusing on that part of the code. But I did see another reason why the right answer is not really 100% right. The use of the NOLOCK hint allows dirty data to be read, so it is possible that the CustomerRefNo returned is not correct. It could even be an illegal value!

Given the available answer options, neither this, nor the use of IN where = would have been better, nor the failure to explicitly specify that MyLinkedServer exists (I know, that should be obvious, but I'm sure people will start complaining about this) should cause any real problems. But it does make me wonder why the question author chose to include the code this way.

A good idea for a question, unfortunately hampered a bit by insufficient attention to detail.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1199766
Posted Thursday, November 3, 2011 3:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:00 AM
Points: 1,011, Visits: 863
Stewart,

I agree that this does not change the aim of the question. I got the question wrong due to not really reading the question correctly after getting hung up on the error in the first line. We use SQL 2005 as our main system so I knew that it would not work so I just tried to work out which of the error messages made most sense.

However, since I have never submitted a QotD I am sure that trying to cover all the various options when saying what versions/configurations it should work with is not easy.

Thanks for the question.
Post #1199771
Posted Thursday, November 3, 2011 5:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:42 AM
Points: 1,181, Visits: 2,652
Excellent question if you ignore the minor errors. Made me investigate a feature I was not aware of.

Thank you


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1199813
Posted Thursday, November 3, 2011 7:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 8,844, Visits: 9,406
Nice question. Pity it says 2005 onwards instead of 2008 onwards, as none of the answers match 2005 behaviour.

I'm not sure whether to agree with Hugo's interpretation of "correct" or disagree. Surely the "correct" value is whatever value (whether current or future or never to happen, ie whether clean or dirty) the query finds if the query is intended to find that value, which is surely what NOLOCK means (and what the writer of the query intended if competent to use that hint). Saying "not correct" suggests that the writer of the query is not competent to use the hint that he wrote, which is often true for that much misused hint, but sometimes false.


Tom
Post #1199896
Posted Thursday, November 3, 2011 7:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:15 AM
Points: 1,432, Visits: 1,059
philip.cullingworth (11/3/2011)
You can not set the value of a variable at the same time as declaring it until SQL 2008.


Well, color me pleased! Until now, I never realized you COULD set the value of a variable at the same time as declaring it on SQL 2008.

.
Post #1199898
Posted Thursday, November 3, 2011 8:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 977, Visits: 939
That was realy nice, thank you

Iulian
Post #1199921
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse