Thursday, February 1, 2018

SQL SERVER : Reading multiple result returned from Store Procedure


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.


No comments:

Post a Comment