Using Stored Procedures

A stored procedure is a precompiled set of SQL statements stored within a database that can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data and improving the efficiency of your reports (stored procedures only need to be written once). Netcool Reporter allows you to access and run your existing stored procedures from within the Report Designer. Netcool Reporter supports stored procedures that pre-process or post-process data from current tables in the database.

The Procedures tab on the Report Designer only appears when your user configuration setting Enabled Stored Procedures is set to True. This setting is determined by your Netcool Reporter administrator.

 

   Note:


    Stored procedures are not supported in SQL mode.


Stored Procedure Types

To run stored procedures in the Report Designer, you must first create stored procedures in your database in order for them to become available for your use. Netcool Reporter supports three types of stored procedures:

•   Procedures with no return value.

•   Procedures with data returned in a table structure.

•   Procedures containing parameters.

Selecting Stored Procedures

When you open the Procedures tab, shown below, the Report Designer retrieves a list of accessible stored procedures and displays them in the Stored Procedures list.

 

   Figure 79: Stored Procedures Tab

The stored procedures are sorted in ascending alphabetical order.

Adding a Stored Procedure

To add a stored procedure:

1.   Click the stored procedure you would like to run from the Stored Procedures list.

2.   Click the Add> button to the left of the Pre Process for Report or Post Process for Report fields. You may select one stored procedure for pre-processing and one stored procedure for post-processing.

Removing a Stored Procedure

To remove a stored procedure, click the procedure name in the Pre Process for Report or Post Process for Report lists and click the associated Remove button.

Determining the Execution Order of Stored Procedures

It is important to arrange the order in which you run stored procedures to ensure that they are executed at the proper stage of your database query. For example, a stored procedure could limit the data set prior to processing the report so that your query has a smaller data set to process. If you add a stored procedure to the Pre Process for Report list, it runs before your database query. If you add a stored procedure to the Post Process for Report list, it runs after your database query.

Adding Parameters to Stored Procedures

To set the values of a stored procedure parameter, click the Set Parameters button below the Pre Process for Report and Post Process for Report lists. The Edit Parameter window is displayed, as shown below.

 

   Figure 80: Edit Parameter Window

For a description of the Edit Parameter window, see Creating Parameterized Reports on page 144.

 

   Note:


    The Lookup button on the Edit Parameter window is grayed out, indicating that it is not possible to look up values for a stored procedure parameter.


The stored procedure may contain more than one parameter. Select the parameters you want to edit from the Parameter Name: drop-down list.

The text that you enter in the Description: field is used at runtime as the prompt text in the Enter Parameter Values window. You must enter values for each parameter in the Enter Parameter Values window by selecting the parameters in turn from the drop-down list.

For details of running a parameterized report, see Entering Parameters When You Run a Report from the Netcool Reporter Application on page 148 and Entering Parameters When You Run a Report from the Netcool Reporter Database on page 149.

 

   Note:


    The input DATE format for the parameter value has to conform to the following format for all databases: yyyy-mm-dd [hh:mm[:ss]].