stored procedures from ASP

stored procedures from ASP

Post by Bria » Wed, 09 May 2001 01:02:11



I've written a stored procedure which generates a random recordset (generated by
using a cursor to populate a temp table).  In ASP, it appears that the final
select statement of the procedure is not returned to the recordset object...

Rs = Cmd.Execute()

How do you ensure that the final SELECT statement of a complex stored procedure
will be returned to the recordset object in ASP?

--Brian

 
 
 

stored procedures from ASP

Post by Aaron Bertrand [MVP » Wed, 09 May 2001 01:23:20


Make sure you use SET NOCOUNT ON at the beginning of your proc, and SET
NOCOUNT OFF just before your final SELECT (which contains the results you
are after in the ASP code).

Otherwise, figure out how many resultsets are being returned to ASP, and
call

    set rs = rs.nextrecordset()

until you get to the one you need.  I'll leave it as an exercise to the
reader to determine which is more efficient.  :-)

For more info on executing SPs from ASP, see
http://www.aspfaq.com/faq/sp.asp


I've written a stored procedure which generates a random recordset
(generated by
using a cursor to populate a temp table).  In ASP, it appears that the final
select statement of the procedure is not returned to the recordset object...

Rs = Cmd.Execute()

How do you ensure that the final SELECT statement of a complex stored
procedure
will be returned to the recordset object in ASP?

--Brian

 
 
 

stored procedures from ASP

Post by Andrew J. Kell » Wed, 09 May 2001 02:07:13


Make sure you SET NOCOUNT ON at the beginning of the sp and if you only want
to return 1 rs then insert the intermediate result sets into a temp table(s)
and issue one last select from it.

--
Andrew J. Kelly
Targitmail.com


I've written a stored procedure which generates a random recordset
(generated by
using a cursor to populate a temp table).  In ASP, it appears that the final
select statement of the procedure is not returned to the recordset object...

Rs = Cmd.Execute()

How do you ensure that the final SELECT statement of a complex stored
procedure
will be returned to the recordset object in ASP?

--Brian