SQL 2K5 - Table-Valued Parameters

Question

SQL 2K5 - Table-Valued Parameters

abmore

Hall of Fame

Points: 3628
More actions
January 15, 2010 at 12:10 am

#386104

Comments posted to this topic are about the item SQL 2K5 - Table-Valued Parameters

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

OCTom SSChampion Points: 11785 More actions · Answer 1

I'm kind of confused by the question. I thought it was discussing table variables. Maybe it's too early in the day, yet. 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table-valued parameters - http://msdn.microsoft.com/en-us/library/bb510489.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ken SSCoach Points: 17191 More actions · Answer 3

This is where my lack of experience with SQL 2008 bites me again. I too thought the question was referencing table variables. Good question, I learned something new!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

yep it is too early in the day, I've got to stop answering these before I've had some caffiene 😉

Kevin Gill SSCrazy Points: 2348 More actions · Answer 5

To my mind, this question could/should have been moderated and edited to make it clearer.

Even though I would have got it wrong had it actually been asking what I thought it was, the fact that it was pretty badly written added an additional, unnecessary level of misdirection that just gets in the way.

The 'correct' answer does not in itself actually answer the question - I have no more idea now than I had ten minutes ago whether table VARIABLES in SQL2K5 can be set to NULL, even though I did actually know that you can't have table parameters in SQL 2K5 (as they would have solved a lot of my problems last year). I skimmed the answers, thought 'I know SQL2K5 can have table variables' (I admit - misreading on my part but I assumed that if it was a 'trick' question then that answer would refer to all parts of the question equally, given that there's only single word answers for the other options) and gave some thought to whether a table VARIABLE can be set to NULL or not. I made my choice, got it wrong, but didn't actually find out the answer.

So can table variables be set to NULL? Perhaps this could be added to the explanation...

-------------------------------Oh no!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Kevin Gill (1/15/2010)
To my mind, this question could/should have been moderated and edited to make it clearer.
Even though I would have got it wrong had it actually been asking what I thought it was, the fact that it was pretty badly written added an additional, unnecessary level of misdirection that just gets in the way.
The 'correct' answer does not in itself actually answer the question - I have no more idea now than I had ten minutes ago whether table VARIABLES in SQL2K5 can be set to NULL, even though I did actually know that you can't have table parameters in SQL 2K5 (as they would have solved a lot of my problems last year). I skimmed the answers, thought 'I know SQL2K5 can have table variables' (I admit - misreading on my part but I assumed that if it was a 'trick' question then that answer would refer to all parts of the question equally, given that there's only single word answers for the other options) and gave some thought to whether a table VARIABLE can be set to NULL or not. I made my choice, got it wrong, but didn't actually find out the answer.
So can table variables be set to NULL? Perhaps this could be added to the explanation...

declare @TestTab table (TableID int, TableData varchar(32));

set @TestTab = null;

Result:

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@TestTab".

Kevin Gill SSCrazy Points: 2348 More actions · Answer 7

In fact, I'll go further (and you can't stop me ;o))

Whenever I miss the intended point of one of these questions, it's usually because I've not paid attention to the title, or because I've formulated my opinion as to the correct answer based on the version that appears in the daily email. The daily email of course does not include the title, or the answers.

Maybe if (as a minimum) the title of the question were included in the mail, I'd be more likely to know what subtleties to be looking for when I came to the site to answer, rather than just clicking the answer I'd already decided on and then wishing to express my opinions on the same when I believe the question is too much of a 'trick'.*

Anyway I'm done. Like I say, I'm not bothered that I got it wrong, just that it was hard for the wrong reasons.

* A trick question, to clarify my meaning is any question where there are a set of obvious answers (e.g. Yes/No) where the actual answer is none of those, and is a reference to some other subtlety involved in the process - one where you have to read the title and all the answers to know what it's actually asking...

-------------------------------Oh no!

Kevin Gill SSCrazy Points: 2348 More actions · Answer 8

Lynn Pettis (1/15/2010)

declare @TestTab table (TableID int, TableData varchar(32));

set @TestTab = null;

Result:

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@TestTab".

Cheers - I could have just tested it 🙂 but I was too busy writing... And I would have got it right, had it been asking what I believed it to be asking 🙂

-------------------------------Oh no!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Kevin Gill (1/15/2010)
In fact, I'll go further (and you can't stop me ;o))
Whenever I miss the intended point of one of these questions, it's usually because I've not paid attention to the title, or because I've formulated my opinion as to the correct answer based on the version that appears in the daily email. The daily email of course does not include the title, or the answers.
Maybe if (as a minimum) the title of the question were included in the mail, I'd be more likely to know what subtleties to be looking for when I came to the site to answer, rather than just clicking the answer I'd already decided on and then wishing to express my opinions on the same when I believe the question is too much of a 'trick'.*
Anyway I'm done. Like I say, I'm not bothered that I got it wrong, just that it was hard for the wrong reasons.
* A trick question, to clarify my meaning is any question where there are a set of obvious answers (e.g. Yes/No) where the actual answer is none of those, and is a reference to some other subtlety involved in the process - one where you have to read the title and all the answers to know what it's actually asking...

When taking a test, particularly a multiple choice (multiple guess, if you prefer) don't you read the entire question and the answers before actually answering the question?

Kevin Gill SSCrazy Points: 2348 More actions · Answer 10

Lynn Pettis (1/15/2010)
When taking a test, particularly a multiple choice (multiple guess, if you prefer) don't you read the entire question and the answers before actually answering the question?

I do, but in this case (as it's not a test and is actually probably only about 30 seconds of an average day) I tend to read QOTD in the email, click 'Answer this' and then pick my pre-decided answer for questions which appear to be yes/no or another obvious combination - questions such as 'Can you...' or 'is it possible to...', in which case I don't have visibility of the title or the answers, and it's actually probably the title which would have helped most in this case. Plus you read the answers to decide which one answers the question, not to work out what it is that the question is asking you. I've often wished the QOTD in the email included the title and it can't be that hard to include it.

I do like QOTD, and I've learnt a lot from getting things wrong. I just ended up feeling like this one taught me nothing, and was just difficult for the sake of it.

-- Kev

-------------------------------Oh no!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

I don't read the QotD in the newsletter, I read it on SSC.

Mauve SSChampion Points: 11316 More actions · Answer 12

Lynn Pettis (1/15/2010)
I don't read the QotD in the newsletter, I read it on SSC.

And it is best to answer later in the day as the question is usually rewritten, often totally different than what was put in the email.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Mauve (1/15/2010)
Lynn Pettis (1/15/2010)
I don't read the QotD in the newsletter, I read it on SSC.
And it is best to answer later in the day as the question is usually rewritten, often totally different than what was put in the email.

Noticed that I have. Makes comments in discussion slightly confusing at times.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Mauve (1/15/2010)
Lynn Pettis (1/15/2010)
I don't read the QotD in the newsletter, I read it on SSC.
And it is best to answer later in the day as the question is usually rewritten, often totally different than what was put in the email.

Sometimes that is true. When rewritten it is generally more precise in the question and the answers also could have changed depending on the morning discussion of the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events