Returning a DataSet from a Stored Procedure within a S.P.

[dwatkins dirq net]

I've been trying to consolidate/reuse some business logic in a recent project and doing so would require me (within a MS-SQL 2000 stored procedure) to get a dataset from a stored procedure and aggregate some data. Is there a way to get a dataset from a stored procedure and put it in a temporary table? This is not an output variable, just the regular output dataset. I'm using Microsoft SQL 2000. Help please!

USE PD9

DECLARE @RC int
DECLARE @datebegin varchar(10)
DECLARE @dateend varchar(10)

SET @datebegin = '01/01/2007'
SET @dateend = '01/15/2007'

--this is a dataset of our sales reps
DECLARE sales_cursor CURSOR
FOR
   	SELECT DISTINCT
		[ABAN8]
	FROM 
		[vF0101_RepDetails]
	ORDER BY [ABAN8]

OPEN sales_cursor

DECLARE @an8 int

FETCH NEXT FROM sales_cursor INTO @an8

WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
	-- i'm looping through a dataset of our sales reps
        -- now i need to get a dataset from the below sproc
        -- loop through that dataset and put it in a temp table
        -- the problem is getting the dataset from the next line - HOW??
	-- EXEC @RC = [PD9].[dbo].[usp_rs_getSalesShipments] @an8, @datebegin, @dateend
	-- print @RC
   END
   FETCH NEXT FROM sales_cursor INTO @an8
END
CLOSE sales_cursor
DEALLOCATE sales_cursor

Dirk Watkins

[andyharman]

You can use:

INSERT INTO #MyTempTable (col1, col2, col3, ..., coln)
  EXEC MyStoredProc

where the temporary table's column list matches the columns returned in the stored procedures resultset (and their data types).