A stored procedure is a set of precompiled and preoptimized SQL statements that performs some database operation. Stored procedures reside where the database resides, and you can access them as needed.
Defining data using a stored procedure
You can specify a stored procedure as the data source for a report if your DBMS supports stored procedures.
For information on support for stored procedures, see your database documentation.
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 report wizards only if the database to which you are connected supports stored procedures.
To define the data using Stored Procedure:
-
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.
-
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.
-
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.
InfoMaker 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
InfoMaker records your preference for building result set descriptions for stored procedure reports in the variable Stored_Procedure_Build in the InfoMaker initialization file. If this variable is set to 1, InfoMaker will automatically build the result set; if the variable is set to 0, you are prompted to define the result set description.
-
-
Continue in the Report wizard as needed for the presentation style you are using.
When you have finished interacting with the wizard, you go to the Report painter with the columns specified in the result set placed in the report.
For information about defining retrieval arguments for reports, see Enhancing Reports
Editing a result set description
After you create a result set that uses a stored procedure, you can edit the result set description from the Report painter.
To edit the result set description:
-
Select Design>Data Source from the menu bar.
This displays the Column Specification view if it is not already displayed.
-
Select Stored Procedure from the Column Specification view's pop-up menu.
The Modify Stored Procedure dialog box displays.
-
Edit the Execute statement, select another stored procedure, or add arguments.
The syntax is:
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.
-
When you have defined the entire result set, click OK.
You return to the Report painter with the columns specified in the result set placed in the report.
For information about defining retrieval arguments for reports, see Enhancing Reports
