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

• Microsoft Access 2000

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).

The Stored Procedure executed successfully but did not return records.

Additional query words: pra prb

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb