Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: Stored Proc Output Param Empty

    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?

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    It may help to know that the provider is "SQLOLEDB"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •