SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameters


Parameters

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27495 Visits: 7549
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1680 Visits: 997
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17139 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27495 Visits: 7549
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27364 Visits: 12738
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1680 Visits: 997
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.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5106 Visits: 2949
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

Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39688 Visits: 12890
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.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3555 Visits: 1248
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