| Home | Previous Lesson: Executing a Remote Stored Procedure Next Lesson: Retrieving the Value Of OUTPUT Variable |
Before talking about Multiple result sets, let's be clear about a result set. It means, the rows returned by one SELECT statement. In SQL Server, you can have as many SELECT statements as you want, and each of those SELECT statements can return rows. The columns that are returned by each SELECT statement could be different. For example:
Declare ProcDummy
AS
/* Result set 1, returns everything from product_master */
select * from product_master
/* Set 2, returns everything from trans table */
select * from trans
/* Set 3, returns the no. of rows for the above SELECT */
select @@rowcount
/* Not a result set, because, we are using SELECT statement
to assign a value to a variable.*/
select @dummy = 100
/* Set 4, returns everything from units table */
select * from units
return 0
While executing multiple result set stored procedures in PowerBuilder, you can retrieve only one set at a time. When the script fetches the first result set, SQLCODE is populated with the 100 return code. From then onwards, the values of the second result set are available to the script.
For example, the following script would execute sp_help Stored Procedure. If you supply a table name as parameter, it would give five result sets. The script listed below fetches the first two result sets:
// This script assumes there are 2 DataWindow controls in
// the window with appropriate columns and data types.
String lObjectName, lOwner, lObjectType, lDataSegment
Long lNewRow
DateTime lCreationTime
lObjectName = "sysobjects"
DECLARE sp_help_proc PROCEDURE FOR dbo.sp_help
@objname = :lObjectName ;
EXECUTE sp_help_proc ;
If SQLCA.SQLcode <> 0 Then
MessageBox( "Error", SQLCA.SQLErrorText )
Close sp_help_proc ;
Return
End If
dwc_1.Title = "Result Set: #1"
Do While True
Fetch sp_help_proc
INTO :lObjectName, :lOwner, :lObjectType ;
If SQLCA.SQLcode = 100 Then Exit
lNewRow = dwc_1.InsertRow(0)
dwc_1.SetItem( lNewRow, 1, lObjectName )
dwc_1.SetItem( lNewRow, 2, lOwner )
dwc_1.SetItem( lNewRow, 3, lObjectType )
Loop
dwc_2.Title = "Result Set: #2"
Do While True
FETCH sp_help_proc
INTO :lDataSegment, :lCreationTime ;
If SQLCA.SQLCOde = 100 or SQLCA.SQLCode = -1 Then Exit
lNewRow = dwc_2.InsertRow(0)
dwc_2.SetItem( lNewRow, 1, lDataSegment )
dwc_2.SetItem( lNewRow, 2, lCreationTime )
Loop
Close sp_help_proc ;
| Home | Previous Lesson: Executing a Remote Stored Procedure Next Lesson: Retrieving the Value Of OUTPUT Variable |