February 25, 2014 at 6:15 pm
I have been working with SQL Server (2005, 2008R2 & 2012) for about six years now and have recently decided that I should start down the path of earning certifications. Part of what is available to me at work is a CBT Nuggets license, which has been wonderful, and I am currently using the Self Test feature, also great. I have been going through the flash cards focusing on the areas I need work on and I have a question I am a bit stumped with.
The actual question along with their answer is below. My question is: Why is "a stored procedure" the object type that fits the description? A stored procedure can do many things, but filtering on a primary key of a table is very rarely the main application for them. Also, why wouldn't "a view" be a possible answer? I can define a view and filter on the tables primary key, would that not be correct? Am I missing something here? Maybe I'm just not thinking about it from the right angle....
Question:
What object type is used to return certain columns from a table based on the primary key column of that table?
Answer:
A stored procedure
Thanks for your help,
Ryan
February 26, 2014 at 12:39 am
Weird question.
I think the key here is 'return certain columns', not return certain rows. A view or function can indeed return different rows based on a pk value, but to return different columns based on a pk value would require dynamic SQL, which neither a view nor a function can use.
That said, if I saw code from one of my devs which used a table's pk value to decide which columns a procedure should return, they'd be taking their code back to the drawing board, it's a pretty odd thing to do.
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
February 26, 2014 at 8:08 am
Ah, thanks, I was thinking of rows, not columns. I see what you're saying and that does seem like an odd thing to do.
I'll mark that as something to remember yet never use! 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply