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

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:

First, complete the steps in "Steps to Reproduce Problem" section of the "More Information" section later in this article.

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

Open the sample Access project, NorthwindCS.adp.
In the Database window, click Stored Procedures under Objects, and then click New.

In a new stored procedure window, enter the following text:

CREATE PROCEDURE Sqroot
   (
      @EnterValue int,
      @Result int OUTPUT
   )
AS
   SELECT @Result = SQRT(@EnterValue)

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.