Select and EXEC in a single statement - SQL server

Question

I have a temp table in a SP. I insert some values into it. I will need to then EXEC a second SP by sending in the values from the temp table. I would rather avoid having to use local variables.

DECLARE @tmp TABLE
(
  Name VARCHAR(200).
  Code INT
)

INSERT INTO @tmp
SELECT 'TEST', 100

EXEC MyProc
  @Name = --Here I send the values from temp
  @Code =

Answer 1

You could wrap it all in a single SP.

Answer 2

You can't, unless you're on SQL Server 2008 which allows table data types. Which can be used as stored proc parameters.

Otherwise frp SQL 2000/2005, for 10 rows in the temp table, you need loop through 10 inner stored proc calls.

Answer 3

create an actual temp table #TempTable and not a @varibale table, and you can share it between parent and child stored procedures...

http://www.sommarskog.se/share_data.html#temptables