I found a pretty good article detailing how to go about passing table data around and it mentions that the INSERT EXEC style table data sharing (http://www.sommarskog.se/share_data.html#INSERTEXEC) has the drawback of not being allowed to be nested?
In other words [in SQL Server 2005 at least], in the pseudocode below PROC1's INSERT EXEC would error out at runtime. I was wondering if anyone knows why this is.
CREATE PROC1
AS
--Fill table variable with data from somewhere
INSERT INTO @tbl EXECUTE spI_Return_Data
-- Do some stuff to the data
-- 'Return' it
SELECT * FROM @tbl
GO
CREATE PROC2
AS
--Fill table variable with data from PROC1
INSERT INTO @tbl EXECUTE PROC1
-- Do some stuff to the data
-- 'Return' it
SELEC * FROM @tbl
GO