Define Stored Procedures

Before you can define a method that calls a database stored procedure, you must describe it to Data Access Builder. To view the list of stored procedures that have been defined within Data Access Builder, open the Stored Procedures List window by doing one of the following actions:

If the stored procedure you want to work with is not listed in the Imported Stored Procedures List in the Stored Procedures List window, then you must add the definition to Data Access Builder. There are two ways to define the stored procedure:

  1. Import the definition from the database.
  2. Define the stored procedure manually.

The following sections describe how to define the stored procedures by each of these methods.

Importing a Definition from the Database
If the stored procedure that you want to use is defined in the database, and if a database connection is presently available, you can import the definition into Data Access Builder by doing the following steps:

  1. On the Stored Procedures List window, click Get Stored Procedures. This accesses the database and returns a list of available stored procedure definitions to the Stored Procedures in Database list box.
  2. Select the stored procedure definition that you want to import and click Import. It will be added to the Imported Stored Procedure List.
  3. It is advisable that you check the imported procedure definition for accuracy before you map a stored procedure call to it. For information on doing this, see Modify Stored Procedure Definitions.

Manually Defining a Stored Procedure
If the a database connection is not available, or if the stored procedure that you want to use is not defined there, you can provide a definition manually. The definition you provide must accurately reflect the stored procedure on the database. Otherwise, run time errors will occur when the stored procedure call method is invoked.

To define a stored procedure manually, do the following steps:

  1. From the Store Procedures List window, Click Add to launch the Add Stored Procedures window.
  2. In the Stored Procedure field, provide a name for the stored procedure. This is the name that appears in the Imported Stored Procedures List, and in the Procedure Name drop-down list in the Add/Modify Stored Procedure Call notebook.
  3. The Parameters list box contains a list of all parameters defined for the stored procedure. To add a parameter, do the following steps:
    1. Select a Parameter Type, according to what the parameter does within the stored procedure:
      • In - Designates an input parameter to the stored procedure.
      • Out - Designates an output parameter from the stored procedure.
      • In/Out - Designates a parameter that is used for both input and output.
      • Result Column - Designates a column in the result set returned by the stored procedure.
      • Return Value - Designates the return value for the stored procedure.
    2. Select an SQL Name and Type; as appropriate, enter the Length, Precision, and Scale of the SQL parameter.
    3. Click Add.
    4. Adjust its position in the parameter list by using the Move Up and Move Down buttons. Parameters must appear in the same order as they do in the stored procedure on the database.
  4. To work with an existing parameter, select it and do any of the following actions on it:
    1. Delete - Deletes the selected parameter.
    2. Copy - Copies the selected parameter and generates a new, unique name for the new parameter.
    3. Move Up - Moves the selected parameter up in the order of parameters. Parameters must appear in the same order as they do in the stored procedure on the database.
    4. Move Down - Moves the selected parameter down in the order of parameters. Parameters must appear in the same order as they do in the stored procedure on the database.
    5. Modify - Change the Parameter Type, SQL Name, SQL Type, Length, Precision, or Scale, and click Modify.

When you are finished modifying the definition, click OK to exit the dialog, or Cancel to cancel your changes and exit. The method will now appear in the Imported Stored Procedures List in the Store Procedure List window, and will be available for mapping to stored procedure call methods in the Add/Modify Stored Procedure Call notebook.



User-Defined Methods


Display List of Defined Stored Procedures
Add User-Defined Methods That Are Stored Procedure Calls


Sample: Create and Use a Stored Procedure Call on a Persistent Object