Hi,
How can we return multiple recordsets in a stored procedure.
Can i have a sample piece of code on how to do this if this is is possible .

Thanks,
Deepak

In the SP header, you have RESULT SETS option to say how many result sets you intend to pass back to the call

Cheers
Sathyaram

Should be something like this :

CREATE PROCEDURE EXAMPLESP (IN examplevar1 INT, OUT examplevar2 INT )
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE cur1 CURSOR WITH RETURN FOR <select .....>;
DECLARE cur2 CURSOR WITH RETURN FOR <select .....>;
.....
OPEN cur1;
OPEN cur2;
END

Like Sathyaram said, RESULT SETS <num> determines how mant result sets will be returned to the caller.

Thanks for your help on the multiple recordset being returned by a DB2 stored procedure. Now Im facing issue with using this stored procedure in my ASP page through ADO.

Here is a sample of we are tryring to do:

Set oCmd.ActiveConnection = objConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "myProc.PROC1"
Set objRS = oCmd.Execute
If Not objRS.EOF Then
strArrayDetails = objRS.GetRows
Set objRS = objRS.NextRecordset
If Not objRS.EOF Then
strArrayResponse = objRS.GetRows
End If
Set objRS = objRS.NextRecordset
If Not objRS.EOF Then
strArrayOther = objRS.GetRows
End If
End If

The stored proc is returning 3 result sets. The number of rows in the result sets 2 and 3 are correct but their contents are not. Im getting 3 characters in each field and junk white space characters are appended to the the 3 characters.
We executed the procedure in stored proc builder and we see the correct data. The problem is happening only if we try to display the result sets' contents through ADO in VB/ASP.

Any help ASAP is greatly appreciated,
TIA,
asha