Nested Stored Procedure

Question

How can i insert values of a nested stored procedure into a table. For example if i created a stored procedure like this.

    Create procedure New
    begin
        create table #tmp
        (
            id int,
            name varchar(50)
        );

        insert into #tmp
            exec stored_procedure 1,2;

        insert into #tmp
            exec stored_procedure 1,2;
        select * from #tmp
    end

Now if I execute this command, SQL Server will display error:

insert into #table
    exec New;

Does anyone have a solution for this problem? Please share

And if you search on that error - which you did not include - you will find many discussions about the error and possible solutions. Erland has extensive discussion about "sharing" generally [here](http://www.sommarskog.se/share_data.html). — SMor, Aug 22 '20 at 19:32

Rory · Answer 1 · 2020-08-22T19:37:09.687

1

Right now you’re not returning any data from your procedure New. If you want it to return data for your calling code to insert you’ll need a select statement in it.

Eg add a line at the end:

SELECT * FROM #tmp;

Also, you can’t nest INSERT EXEC statements. See this answer for more details

edited Aug 22 '20 at 19:37

answered Aug 22 '20 at 19:23

Rory

40,559
52
175
261

i added that statement as well but sql server give this error,INSERT EXEC statement cannot be nested – Muhammad Hassan Arshad Aug 22 '20 at 19:32
Ah, you’re right, you can’t nest `INSERT EXEC`. See https://stackoverflow.com/a/3795413/8479 – Rory Aug 22 '20 at 19:35

score 0 · Answer 2 · answered Aug 22 '20 at 21:12

0

You can't do a insert with de values returned by a Stored Procedure.

In your case the easiest way is to change de stored procedures by table functions. You can find more about table functions here

My opinion is to make it simple if u can.

answered Aug 22 '20 at 21:12

Miquel Galobardes

41
5

Nested Stored Procedure

2 Answers2