I've created a stored procedure that takes one input parameter and returns one output parameter and a recordset.
The SP looks like this (I trimmed it a bit for readability):
Code:
CREATE PROCEDURE dbo.spEligibleProducts
@prmProdCode nvarchar(20),
@prmTotalCount int Output
AS
SELECT * FROM tblProducts WHERE cProdCode = @prmProdCode
SELECT @prmTotalCount = @@ROWCOUNT
GO
My ASP which executes the ASP looks like this:
Code:
objDataCmd_TD.CommandText="spEligibleProducts" 'query name
objDataCmd_TD.CommandType=adCMDStoredProc 'con connection type
objDataCmd_TD.Parameters.Append objDataCmd_TD.CreateParameter("@prmProdCode", adVarWChar, adParamInput, 20 , sProdCode)
objDataCmd_TD.Parameters.Append objDataCmd_TD.CreateParameter("@prmTotalCount", adInteger, adParamOutput)
set objTempRS = objDataCmd_TD.Execute 'Execute into temporary recordset
iTotalRows = objDataCmd_TD.Parameters("@prmTotalCount") 'Store total rows
sProdCode is defined elsewhere as the Product Code, and iTotalRows is also defined elsewhere to receive the value of the output parameter. The output param is rows returned in the first SELECT of the stored procedure.
When I execute the SQL from a query analyzer, the proper value is returned. When this executes from ASP, iTotalRows is empty. No value at all.
The goal is to get the total records returned, and the recordset from the first query to be traversed after the execution of the SP.
Any ideas?
dBforums
>
Stored Proc Output Param Empty
08-25-03, 18:13
Linear Mode
