I have a SQL Server 2005 stored proc which returns two result sets which are different in schema.
Another stored proc executes it as an Insert-Exec. However I need to insert the first result set, not the last one. What's a way to do this?
I can create a new stored proc which is a copy of the first one which returns just the result set I want but I wanted to know if I can use the existing one which returns two.
Is there a compelling reason why you can't just have that first sproc return only one result set? As a rule, you should probably avoid having one sproc do both an INSERT and a SELECT (the exception is if the SELECT is to get the newly created row's identity).
Actually, INSERT..EXEC will try to insert BOTH datasets into the table. If the column counts match and the datatype can be implicitly converted, then you will actually get both.
Otherwise, it will always fail because there is no way to only get one of the resultsets.
The solution to this problem is to extract the functionality that you want from the called procedure and incorporate it into the (formerly) calling procedure. And remind yourself while doing it that "SQL is not like client code: redundant code is more acceptable than redundant data".
In case this was not clear above, let me delineate the facts and options available to anyone in this situation:
1) If the two result sets returned are compatible, then you can get both in the same table with the INSERT and try to remove the ones that you do not want.
2) If the two result sets are incompatible then INSERT..EXEC cannot be made to work.
3) You can copy the code out of the called procedure and re-use it in the caller, and deal with the cost of dual-editing maintenance.
4) You can change the called procedure to work more compatibly with your other procedures.
Thats it. Those are your choices in T-SQL for this situation. There are some additional tricks that you can play with SQLCLR or client code but they will involve going about this a little bit differently.
Oo to prevent code from getting out of synch between the two processes, why not write a proc that does what you want to for the insert, call that in your process and have the orginal proc call that to get the first recordset and then do whatever else it needs to do.
Depending on how you get to this select, it is possible it might be refactored to a table-valued function instead of a proc that both processes would call.