Here's an example where the output from one sp is passed into a second procedure which is run multiple times using dynamic sql. Maybe something here can help you.
First create some sample data
--create a table for some test data
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[StudentID] INT NULL,
[AmtDue] MONEY NULL,
[DueDate] DATETIME,
PRIMARY KEY (ID))
--generate the test data
INSERT INTO #TempTable
SELECT TOP 10000
StudentID = (SELECT (ABS(CHECKSUM(NEWID()) % 100) * 1))+1,
AmtDue = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
DueDate = DATEADD(year,10,CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME))
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
GO
Now create the test procedures which demonstrate the method
--FIRST procedure takes a single ID as input and outputs the amt due
CREATE PROCEDURE dbo.Test1
@StudentID INT
,@AmtDue MONEY OUTPUT
AS
BEGIN
DECLARE
@DaysOverDue INT
SELECT
@DaysOverDue =
DATEDIFF(DAY,GETDATE(),MAX(tt.DueDate))
FROM #TempTable AS tt
WHERE
StudentID = @StudentID
IF @DaysOverDue >= 0
BEGIN
SELECT
@AmtDue = SUM(AmtDue)
FROM #TempTable
WHERE StudentID = @StudentID
RETURN @StudentID
END
ELSE
BEGIN
SET @AmtDue = 0
RETURN 0
END
END
GO
--SECOND procedure uses the OUTPUT of the first procedure
CREATE PROCEDURE dbo.Test2
@StudentID INT
,@BalanceDue MONEY
AS
BEGIN
SELECT DISTINCT
@StudentID AS StudentID
,@BalanceDue AS BalanceDue
FROM #TempTable
WHERE
StudentID = @StudentID
END
GO
Now create a temp table to hold the results
and run the procedures
--a table to hold the results
IF OBJECT_ID('tempdb..#ResultsTable') IS NOT NULL
DROP TABLE #ResultsTable
CREATE TABLE #ResultsTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[StudentID] INT NULL,
[BalanceDue] MONEY NULL,
PRIMARY KEY (ID))
GO
DECLARE
@maxID INT
,@strSQL NVARCHAR(MAX)
SELECT
@maxID = MAX(StudentID)
FROM
#TempTable
--Run the procedures using dynamicSQL
SELECT
@strSQL =
(SELECT
(SELECT CHAR(10)+N'EXEC @ReturnValue = dbo.Test1 '+CAST(t.N AS NVARCHAR(10))+', @OutputParameter OUTPUT'+CHAR(10)+
'INSERT INTO #ResultsTable EXEC dbo.Test2 @ReturnValue, @OutputParameter'+CHAR(10))
FROM
dbo.Tally AS t
WHERE
t.N <= @maxID
FOR XML PATH(''))
SET @strSQL = N'DECLARE @ReturnValue INT, @OutputParameter MONEY'+CHAR(10)+@strSQL
--PRINT @strSQL
EXEC(@strSQL)
SELECT * FROM #ResultsTable