Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameters


Parameters

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5786 Visits: 7137
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”
philip.cullingworth
philip.cullingworth
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: 1463 Visits: 967
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.
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 4208
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5786 Visits: 7137
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”
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11578
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
philip.cullingworth
philip.cullingworth
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: 1463 Visits: 967
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.
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
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

TomThomson
TomThomson
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: 10711 Visits: 12013
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

Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1062
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.

.
Iulian -207023
Iulian -207023
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1226
That was realy nice, thank you

Iulian
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