<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 1 of 3
1
2
3
»
»»
Parameters
Rate Topic
Display Mode
Topic Options
Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Wednesday, November 02, 2011 10:14 PM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125,
Visits: 4,311
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
philip.cullingworth
philip.cullingworth
Posted Thursday, November 03, 2011 2:35 AM
Right there with Babe
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:05 AM
Points: 752,
Visits: 713
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
MarkusB
MarkusB
Posted Thursday, November 03, 2011 2:42 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 4,218,
Visits: 3,875
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Thursday, November 03, 2011 2:55 AM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125,
Visits: 4,311
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
Hugo Kornelis
Hugo Kornelis
Posted Thursday, November 03, 2011 3:07 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,235,
Visits: 7,041
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
philip.cullingworth
philip.cullingworth
Posted Thursday, November 03, 2011 3:13 AM
Right there with Babe
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:05 AM
Points: 752,
Visits: 713
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
nigel.
nigel.
Posted Thursday, November 03, 2011 5:02 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,164,
Visits: 2,601
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
L' Eomot Inversé
L' Eomot Inversé
Posted Thursday, November 03, 2011 7:44 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088,
Visits: 7,143
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1199896
Rose Bud
Rose Bud
Posted Thursday, November 03, 2011 7:45 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:42 AM
Points: 1,072,
Visits: 1,026
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
Iulian -207023
Iulian -207023
Posted Thursday, November 03, 2011 8:12 AM
SSC Eights!
Group: General Forum Members
Last Login: Saturday, March 16, 2013 9:53 AM
Points: 847,
Visits: 768
That was realy nice, thank you
Iulian
Post #1199921
« Prev Topic
|
Next Topic »
29 posts, Page 1 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.
