I am uisng following code. How do create a table for insert into when 2 resul sets are differnt? Please advice.
CREATE procedure PROC1 as
DROP PROCEDURE PROC2
Create procedure PROC2 as
INSERT INTO #temp
SELECT * FROM #temp
I think you might find that INSERT...EXEC will let you down in that instance. I don't believe you can capture multiple resultsets with it.
In fact, as I think about it, the only way that comes to my mind to handle that kind of thing anywhere near gracefully would be with the CLR.
I'm assuming you don't have the option of altering the Procedure itself?