Microsoft KB Archive/279821

= ACC2002: Default Parameter Is Automatically Applied to Stored Procedures =

Article ID: 279821

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q279821



Moderate: Requires basic macro, coding, and interoperability skills.

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



SYMPTOMS
When you use the Default keyword with a parameterized stored procedure, you are not prompted to provide a value for the parameter. Instead, the stored procedure automatically runs using the default value for the parameter.



RESOLUTION
To ensure that you are prompted with default values, use a SQL function instead of a stored procedure. To create a function with default values, follow these steps:  Open the sample project NorthwindCS.adp. On the Insert menu, click Query. In the New Query dialog box, click Design In-Line Function, and then click OK. Click Close to close the Add Table dialog box without adding a table. On the View menu, click SQL View.  Add the following Transact SQL in the SQL Text Editor: CREATE FUNCTION fnCustOrders (@EnterCustomerID varchar(5)='WOLZA') RETURNS TABLE AS     RETURN (SELECT dbo.Customers.*              FROM dbo.Customers              WHERE (CustomerID = @EnterCustomerID)) </li> On the File menu, click Save.</li> Save the function as fnCustOrders, and then close it.</li> Run the fnCustOrders function from the Database window.</li> In the Enter Parameter Value dialog box, click the arrow, and then click <DEFAULT>.</li> Click OK.</li></ol>

Note that the function returns the record where the CustomerID field equals &quot;WOLZA&quot;, because that is the default value.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> Open the sample project NorthwindCS.adp.</li> On the Insert menu, click Query.</li> In the New Query dialog box, click Create Text Stored Procedure, and then click OK.</li>  Insert the following Transact SQL in the Text Editor: CREATE PROCEDURE spCustOrders @EnterCustomerID varchar(5) = 'WOLZA' AS         SELECT * FROM Customers WHERE CustomerID = @EnterCustomerID </li> Save the stored procedure as spCustOrders, and then close it.</li> Run the spCustOrders stored procedure from the Database window.</li></ol>

Note that the stored procedure does not prompt you for a parameter. It immediately returns the record where CustomerID equals &quot;WOLZA.&quot;

Additional query words: prb

Keywords: kbprb kbclientserver KB279821

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.