I see lot of posts where people want to insert the values returned from the store procedure.
Any versions of SQL SERVER don't give you that privilege to insert the values in to temp table if the store procedure returns multiple result set.
Example of procedure returning only one result set :
CREATE PROCEDURE PR_MacTest
AS
begin
select TOP 10 ref from Users
END
CREATE TABLE #TTMAC
(
name varchar(10)
)
INSERT INTO #TTMAC
EXEC PR_MacTest
select * from #TTMAC
Now we go further and alter the procedure PR_MacTest to return multiple result set
ALTER PROCEDURE PR_MacTest
AS
begin
select TOP 10 ref from Users
select TOP 2 ref from Users
END
IF YOU TRY TO DO THE SAME YOU WILL END UP WITH BELOW ERROR :
Msg 8164, Level 16, State 1, Procedure PR_MacTest, Line 27
An INSERT EXEC statement cannot be nested.
The only thing you can do over here is write an work around if you dont want to do any changes in your existing store procedure. Write an console application and call the procedure and you can do further filtration with result set on the data returned via called the procedure.