Using Stored Procedure – PB Docs 2021

If the Stored Procedure icon is not displayed

The icon for the Stored Procedure data source displays in the
Choose Data Source dialog box in the DataWindow object wizards only if
the database to which you are connected supports stored
procedures.

To define the data using Stored Procedure:

1. Select Stored Procedure in the Choose Data Source dialog box
   in the wizard and click Next.

   The Select Stored Procedure dialog box displays a list of the
   stored procedures in the current database.

2. Select a stored procedure from the list.

   To list system procedures, select the System Procedure check
   box.

   The syntax of the selected stored procedure displays below the
   list of stored procedures.

3. Specify how you want the result set description built:

   • To build the result set description automatically, clear
     the Manual Result Set check box and click Next.

     PowerBuilder executes the stored procedure and builds the
     result set description for you.

   • To define the result set description manually, select the
     Manual Result Set check box and click Next.

     In the Define Stored Procedure Result Set dialog
     box:

     • Enter the name and type of the first column in the
       result set.

     • To add additional columns, click Add.

   Your preference is saved

   PowerBuilder records your preference for building result set
   descriptions for stored procedure DataWindow objects in the
   variable Stored_Procedure_Build in the PowerBuilder initialization
   file. If this variable is set to 1, PowerBuilder will
   automatically build the result set; if the variable is set to 0,
   you are prompted to define the result set description.

4. Continue in the DataWindow wizard as needed for the
   presentation style you are using.

   When you have finished interacting with the wizard, you go to
   the DataWindow painter with the columns specified in the result set
   placed in the DataWindow object.

   For information about defining retrieval arguments for
   DataWindow objects, see Enhancing DataWindow
   Objects.

   For information about using a stored procedure to update the
   database, see Using stored procedures to
   update the database.

To edit the result set description:

1. Select Design>Data Source from the menu bar.

   This displays the Column Specification view if it is not
   already displayed.

2. Select Stored Procedure from the Column Specification view’s
   pop-up menu.

   The Modify Stored Procedure dialog box displays.

3. Edit the Execute statement, select another stored procedure,
   or add arguments.

   The syntax is:

   PowerBuilder

   execute sp_procname;num arg1 = :arg1, arg2 = :arg2..., argn =:argn

   1	execute sp_procname;num arg1 = :arg1, arg2 = :arg2..., argn =:argn

   where sp_procname is the name of the stored procedure, num is
   the stored procedure group suffix, and arg1, arg2, and argn are the
   stored procedure’s arguments.

   The group suffix is an optional integer used in some DBMSs to
   group procedures of the same name so that they can be dropped
   together with a single DROP PROCEDURE statement. For other DBMSs the
   number is ignored.

4. When you have defined the entire result set, click OK.

   You return to the DataWindow painter with the columns
   specified in the result set placed in the DataWindow object.

   For information about defining retrieval arguments for
   DataWindow objects, see Enhancing DataWindow
   Objects.