sql-servert-sqlsql-server-2008stored-proceduresoutput-clause

OUTPUT clause for Stored Procedure vs Table-Valued Function

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?

Solution

  • 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."