OUTPUT clause for Stored Procedure vs Table-Valued Function

Question

I'm studying for the MCTS 70-433 "Database Design" cert, and in the text that I'm studying, one of the self-tests has this question.

You have a stored procedure named Get_NewProducts. You wish to insert the results of this stored procedure into the Production.Product table and output the INSERTED.* values using the OUTPUT clause. What is the best way to do this?

There are four possible answers. The first three choices are all variations of an "INSERT...OUTPUT...EXECUTE Get_NewProducts" statement. 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 it. Anyone have any insights?

Answer 1

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

• Stored procedures can be and often are nested. Procedure A calls B, which calls C, and so on.
• The code called by an INSERT...EXECUTE... statement cannot itself contain or reference an INSERT...EXECUTE... statement. If you put one in, you cannot then "embed" this procedure in a later INSERT...EXECUTE...

This may seem trivial, and it generally is, at least until you hit it during a refactoring project. Once bitten, twice shy. (And it's bit me a number of times.)

There are a number of style and appearance reasons as well, but they're kind of superficial. There probably is a serious technical reason, perhaps having to do with recompiles or query execution plans; if so, hopefully someone else will post them.

Answer 2

Just one reason their "right answer" is not right: TVFs have issues with error checking and reporting.

It's a really odd question/answer because D doesn't even seem to be a possibility given the question.

Answer 3

I don't know a 'correct' answer, but I guess that the thinking of the author is that 70-433 Database Development is development and design oriented exam, as opposed to say one of the 'data access' exams like 70-442. During the design phase you should be able to spot faults in the existing system and propose better solutions. The author considers that stored procedure that needs to have its output inserted into a table is better off rewritten as a TVF. You'll find both cons and pros as whether a TVF is better than a proc (insert exec nesting pro, bad error handling con just to start with).

I took some of these exams myself and I found that the exam preparation material and the exams themselves are not always the absolute ultimate reference on their subject. On the large they are correct and good value, but they have problems here and then and I found at least some questionable recommendations and even plain wrong ones. And on the topics that I found to be wrong I actually am the ultimate reference on the subject, they were covering code that I wrote on features I designed...

My advice is to get a feel of what is 'expected' answer and be prepared for it during the actual exam. Given your flair points and your answer I've seen, you are already above the exam level, so just go through the hoops, earn your exam badge and move on.

Answer 4

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 an execute_statement*."