Clever Geek Handbook

OUTPUT Output for Stored Procedure and Table Function

I am studying for the MCTS 70-433 "Database Development" certificate and in the text I study in one of the self-tests there is this question.

You have a stored procedure named Get_NewProducts. You want to insert the results of this stored procedure into the Production.Product table and enter INSERTED values. * using OUTPUT. What's the best way to do this?

There are four possible answers. The first three options are all options for < INSERT ... OUTPUT ... EXECUTE Get_NewProducts . The fourth choice, D, simply says, "Rewrite the stored procedure as a table-valued function."

D is the correct answer. I don't quite understand why, and there is nothing in the text that explains this. Does anyone have any idea?

+2


source to share


4 answers


Well, from msdn :



"The OUTPUT clause is not supported in DML statements that reference local partitioned views, distributed partitioned views, or remote tables, or INSERT statements that contain execute_statement ."

+5


source


My knee jerk reaction to this (I hit it again a few days ago):

  • Stored procedures can and often be nested. Procedure A calls B, which calls C, and so on.
  • Code called by an INSERT ... EXECUTE ... statement cannot itself contain or reference an INSERT ... EXECUTE ... statement. If you put it, you cannot "inject" that procedure into a later INSERT ... EXECUTE ...


This may sound trivial, and usually it is at least until you hit it during your refactoring project. Idiom: burnt in milk - blowing on water. (And it beat me a few times.)

There are many reasons for style and appearance, but they are superficial. There is likely a serious technical reason, possibly related to recompilation or query execution plans; if so, hopefully someone else will post them.

+1


source


For one reason, their "correct answer" is incorrect: TVF has problems with error checking and reporting.

This is a really strange question / answer because D doesn't even seem possible given the question.

0


source


I don't know the "right" answer, but I think the author's mindset is that 70-433 Database Development

is a development and design exam, as opposed to one of the data access exams like 70-442. During the design phase, you must be able to troubleshoot the existing system and come up with the best solutions. The author believes that a stored procedure that should have its output inserted into a table is best rewritten as TVF. You will find both cons and pros, as TVF is better than proc (embed exec nesting pro, poor error handling to start with).

I have taken some of these exams myself and I have found that the exam preparation material and the exams themselves are not always the absolute end reference to their subject. In general, they are correct and valuable, but here they have problems, and I found at least some dubious recommendations and even simple wrong ones. And for those topics that I think are wrong, I am actually the ultimate link to this topic, they cover the code I wrote about the functions I developed ...

My advice is to understand what the "expected" answer is and be prepared for it during the actual exam. Given your talents and your answer I have seen, you are already above the exam level, so just go through the hoops, earn your exam badge and move on.

0


source






More articles:


All Articles