Using SQLServer 2000
I have a series of SProcs where SP1, invokes SP2, which invokes SP3.
All of these return a recordset and all are functioning properly.
Issue: I need yet another layer of abstraction - another SP that will invoke SP1 iteratively, storing the return recordsets into a temp table and then performing a query and returning the combined RS.
Problem is that SP2 already uses "Insert Into @tmpTable EXEC sp..." and I have found out that nesting is not allowed - so I need to get the returned RecordSets from SP1 into a table another way.
From what I can tell, cursors with SP's are only supported if they are an output parameter, which of course requires a modification to SP1 and all the functions that use it.
Is returning a cursor from SP1 and then fetching each row from the RS the only way to accomplish this?
I would think there is a simple way to output the recordset(s) from SP1 into a temporary table similar to the Insert Into Exec functionality, but I haven't found it.
I have a series of SProcs where SP1, invokes SP2, which invokes SP3.
All of these return a recordset and all are functioning properly.
Issue: I need yet another layer of abstraction - another SP that will invoke SP1 iteratively, storing the return recordsets into a temp table and then performing a query and returning the combined RS.
Problem is that SP2 already uses "Insert Into @tmpTable EXEC sp..." and I have found out that nesting is not allowed - so I need to get the returned RecordSets from SP1 into a table another way.
From what I can tell, cursors with SP's are only supported if they are an output parameter, which of course requires a modification to SP1 and all the functions that use it.
Is returning a cursor from SP1 and then fetching each row from the RS the only way to accomplish this?
I would think there is a simple way to output the recordset(s) from SP1 into a temporary table similar to the Insert Into Exec functionality, but I haven't found it.