Saving RS from a StoredProc

[topry]

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.

 

[JonathanC]

on SP2...create a varchar string of your query using
a variable tablename coming from SP1.

Execute the string using sp_executesql.

 

[topry]

Thanks for the reply. The only problem with changing SP2 was that it would require changing the code of existing functions that already use it. I ended up resolving it by wrapping with another SP that enumerates the result set via a cursor as it requires fewer existing items to be changed.