ACC2002: Default Parameter Is Automatically Applied to Stored Procedures (279821)

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

On the File menu, click Save.
Save the function as fnCustOrders, and then close it.
Run the fnCustOrders function from the Database window.
In the Enter Parameter Value dialog box, click the arrow, and then click <DEFAULT>.
Click OK.

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

MORE INFORMATION

Steps to Reproduce the Behavior

Open the sample project NorthwindCS.adp.
On the Insert menu, click Query.
In the New Query dialog box, click Create Text Stored Procedure, and then click OK.

Insert the following Transact SQL in the Text Editor:

      CREATE PROCEDURE spCustOrders
          @EnterCustomerID varchar(5) = 'WOLZA'	
      AS
          SELECT * FROM Customers WHERE CustomerID = @EnterCustomerID

Save the stored procedure as spCustOrders, and then close it.
Run the spCustOrders stored procedure from the Database window.

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