Is my solution, in my case, for nested INSERT EXEC Statement good?

When I run the script below I get error INSERT EXEC Statement cannot be nested

Some info regard the stored proc:

1. Multiply connections may call stored proc a
2. I can't use UDF because in each proc I need to use IDENTITY_INSERT, try...catch blocks

I thought to create table with name tmp and instead of inserting data into temporary tables in b,c stored proces, they would insert the data in tmp table. To map the rows to specific connection I can add Group column and populate it with @@SPID. In the end of stored proc a I will remove all rows of specific @@SPID so it can be used with other connection. Is this good and effective solution?

Thank you

create procedure a
as
begin try
    declare @tbl table(id int)
    insert into @tbl
    exec b
end try
begin catch
end catch


create procedure b
as
begin try
    declare @tbl table(id int)
    insert into @tbl
    exec c

   select * from @tbl
end try
begin catch
end catch


create procedure b
as
begin try开发者_C百科
    declare @tbl table(id int)

    insert into @tbl(id)
    values(1)

    select * from @tbl
end try
begin catch
end catch

sdfdf

---

You might want to read Erland Sommarskog's article, How to Share Data Between Stored Procedures

---

In general I would use a session specific temp table, that way you have much less overhead managing aborted transactions and such, as it cleans itself up.

The disadvantage is that it is a tad fragile, since dependencies are invisible:

create procedure a
as
    set nocount on
    create table #tbl (id int)
    exec b

    set nocount off
    select * from #tbl
go

create procedure b
as
   set nocount on 

   insert into #tbl
   values(1)
   exec c

go

create procedure c
as
    set nocount on

    insert into #tbl
    values(2)

go 


exec a

I would usually shy away from @@SPID scoping, if you somehow forget to clean up you will have unexpected results.

That said: the solution you use highly depends on the problem at hand Erland's article Alex linked specifies all the options out there.