Result sets with nested stored procedures

[IronIke]

Is there a way, in SQL 7, to build a result set using calls to a stored procedure? I have a stored procedure that returns a result set using a Select statement based on input paramters. I want to have the calling stored procedure return a result set using data from multiple calls to the first stored procedure.

Example:

Create Procedure procSP1
@param1 varchar(30)
As

...

Select col1, col2, col3 From table1 Where xyz=@param1
go

Can I write a second stored procedure that makes multiple calls to procSP1 and returns the result set (col1, col2, col3) from each call in a result set?

This second stored procedure will be called from Crystal Reports.

Thanks in advance.

 

[rajeevnandanmishra]

Hi IronIke,
I think the following can work for you.

1. Create Procedure procSP1 @param1 varchar(30) as ....
2. Create Procedure procSP2 as
CREATE TABLE #myTempTable(Col1 DataType, Col2 DataType, ...)
MyLoopStartHere
INSERT #myTempTable
EXEC procSP1
.
.
.
MyLoopEndHere
SELECT * FROM #myTempTable
return
3. Call the procedure procSP2 from Crystal Report.

Let us know if you face any problem in doing this.