Denali provides limited support for stored procedure result set redefinition (transformation) in an EXECUTE statement. For example, the documentation gives an example that changes column names and data types http://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx. It is very promising to see the ability to do transformations--including those as complex as changing data types!--of stored procedure result sets within SQL Server. It is also great that this feature works with INSERT EXEC. However, it would be excellent if more transformation scenarios were covered.
In particular, I would like:
1. Remove Columns: A flag to specify that all columns not specified are removed from the result set instead of raising an error.
2. Reorder Columns: The ability for the mapping to (optionally) be specified with new and old column names, so that unmapped columns can be detected and columns can be reordered. (Without this, #1 would only be able to remove columns at the end ordinal positions and columns could not be rearranged. Furthermore, this allows the columns to be reordered so they can line up with an INSERT EXEC target table when used together with INSERT EXEC.)
The justification for this is so that INSERT EXEC can be used on a stored procedure allowing for additional columns to be added to the stored procedure later. Presently, adding columns to a stored procedure result set using in an INSERT EXEC without a simultaneous change to the INSERT EXEC statement causes the INSERT EXEC to fail. This severely impacts the composability and maintainability of stored procedures as adding a column to the result set is a common maintenance task.
(Another benefit: The existing data type mapping feature in Denali together with these new improvements will mean that INSERT EXEC can more often be used to source table data from a stored procedure without specially designing the table after the stored procedure.)
The Proposed Solution contains a possible starting point for an enhanced syntax.
(NOTE: A different treatment of the topic of stored procedure composability from 2007 prior to the introduction of EXECUTE WITH RESULT SETS is: https://connect.microsoft.com/SQLServer/feedback/details/294571/improve-insert-exec.)