Stored Procedure Cursors result set

Stored Procedure Cursors result set

Post by Angi » Fri, 29 Mar 2002 07:26:49



Hi there!  I've got a question and I'm hoping ya'll can help me out!

I've got a stored procedure which creates various temp tables.  I am
using cursors to sort through and selectively update some of the rows
in the tables.  Then at the end I want to return select * from #temp
to my COM object.

My problem is that each time I open a cursor, it returns the selected
row to my result set.  So I end up with several random rowsets.  I
need to just return the final result from the select statement at the
end of the procedure.  Is there a property or something I can set on
the cursor so that it will not produce any results?

I appreciate your help!

-Angie

 
 
 

Stored Procedure Cursors result set

Post by Eric Sabin » Fri, 29 Mar 2002 10:17:58



Quote:> Hi there!  I've got a question and I'm hoping ya'll can help me out!

> I've got a stored procedure which creates various temp tables.  I am
> using cursors to sort through and selectively update some of the rows
> in the tables.  Then at the end I want to return select * from #temp
> to my COM object.

> My problem is that each time I open a cursor, it returns the selected
> row to my result set.  So I end up with several random rowsets.  I
> need to just return the final result from the select statement at the
> end of the procedure.  Is there a property or something I can set on
> the cursor so that it will not produce any results?

> I appreciate your help!

> -Angie

Angie, I have two suggestions.
1) Wrap your incidental DML in the SET NOCOUNT ON|OFF statements to prevent
interim statements from being returned as recordsets.
2) Assuming you're using ADO or DAO, use the .NEXTRECORDSET method of the
opened recordset object to traverse through your returned recordsets from
the stored procedure.

hth
Eric