Retrieving Output Parameters and Recordset

[PinkGuava]

Hi,

I have a T-SQL stored procedure that returns both output parameters
and a recordset. How do I retrieve them in my ASP script? As far as I
know, the ADO Command object can be used to retrieve the output
parameters, but will I be able to retrieve the recordset using the
Command object as well? Or do I have to use the Recordset object?

Example of stored procedure:

CREATE PROCEDURE p_GetTestSites
(
@Proceed CHAR(1) OUTPUT,
@ErrMsg VARCHAR(300) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON

/*
some data processing done here
*/

SET @Proceed = 'Y'
SET @ErrMsg = 'Error message'

SELECT TestSite FROM #TestSites

RETURN 0
END

 

[Bob Barrows [MVP]]

Hi,

I have a T-SQL stored procedure that returns both output parameters
and a recordset. How do I retrieve them in my ASP script? As far as I
know, the ADO Command object can be used to retrieve the output
parameters, but will I be able to retrieve the recordset using the
Command object as well? Or do I have to use the Recordset object?

You have to use both, keeping in mind that the all the records produced by
the procedure have to be sent to the client before the output and return
parameter values are sent. For a default server-side forward-only cursor,
this means that the recordset essentially needs to be closed before you can
read the values returned as output and return parameters. This is sort of a
blessing, in that it encourages the use of methods such as GetRows and
GetString to quickly process data.

The technique looks like this:

First, create a Command object (cmd), setting its CommandType and
CommandText properties to point at the stored procedure, and creating its
Parameters collection (using my stored procedure code generator, available
at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear,
makes this job easier)

Then, instead of
cmd.Execute ,,adExecuteNoRecords

You would do this:
Set rs=cmd.Execute
if not rs.eof then arData=rs.GetRows
rs.close:set rs=nothing
outputparmval = cmd.Parameters("@outputparm").value

if isArray(arData) then
'process the array
else
'return message indicating no records were returned
end if

Bob Barrows