ACC2000: Stored Procedure with Output Parameter Doesn't Return Data

Article translations Article translations
Article ID: 225948 - View products that this article applies to.
This article was previously published under Q225948
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

Expand all | Collapse all

On This Page

SYMPTOMS

When you try to run a stored procedure that uses an output parameter to return data from within a Microsoft Access project (.adp), you are prompted for any input parameter(s) as well as the ouput parameter. After you enter the parameter(s), you receive the following message:
The Stored Procedure executed successfully but did not return records.

RESOLUTION

To run a stored procedure that contains output parameters from within an Access project, create a second stored procedure that nests the original procedure. Then you can run the new stored procedure instead. The following example demonstrates how to implement this technique:
  1. First, complete the steps in "Steps to Reproduce Problem" section of the "More Information" section later in this article.
  2. Create a new stored procedure, and enter the following text:
    CREATE PROCEDURE OutputSqrrt
    (
       /* Input Parameter used to prompt user for value */ 
    
       @EnterValue int
     )
    AS
       /* variable used to return the result */ 
       Declare @OutputResult int 
    
       /* Call First Stored Procedure, storing the result in @OutputResult */ 
       Execute Sqroot @EnterValue,  @OutputResult OUTPUT
       
       SELECT  @OutputResult as long
    					

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample Access project, NorthwindCS.adp.
  2. In the Database window, click Stored Procedures under Objects, and then click New.
  3. In a new stored procedure window, enter the following text:
    CREATE PROCEDURE Sqroot
       (
          @EnterValue int,
          @Result int OUTPUT
       )
    AS
       SELECT @Result = SQRT(@EnterValue)
    					
  4. Close and save the stored procedure as "Sqroot" (without quotation marks).
When you run the stored procedure that you've just created, you are prompted for both the input parameter and the output parameter. When you click OK on the prompt for the output parameter, Microsoft Access returns the following message:
The Stored Procedure executed successfully but did not return records.

Properties

Article ID: 225948 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbprb kbclientserver KB225948

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com