views:

56

answers:

1

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
A: 

Internal implementation restrictions.

If you need to capture the output of stored procedures, then those procedures should be a table valued function to start with. Ultimately you can work around the restriction by using CLR procedures.

Remus Rusanu
Thanks, but I'm actually interested in knowing what the "Internal implementation restrictions" are. I'm not sure where to look. I'm not actually looking for design suggestions for sharing table info as the link I provided in my post actually has dozens.
fordareh