Creating a Stored Procedure

How to:

You can code a stored procedure in the text editor feature that is easily accessible from the Web Console and the Data Management Console.

The contents of a procedure can vary greatly. Reports can be created that go to a user’s browser, a configured printer, or held as a data extract in various formats. The reporting and extract language is called TABLE. Additionally, syntax is available to parameterize a request and control the flow of execution in a request. Parameters (called amper variables) and flow of control within a procedure are part of the Dialogue Manager language.

For complete information about TABLE, see the Creating Reports With WebFOCUS Language manual. For complete information about Dialogue Manager commands, see the Developing Reporting Applications manual. While the entire syntax of these features is not within the scope of this manual, it is necessary to provide information about basic run-time essentials. For information, see Procedure Run-Time Processing Essentials.

The following is small representative example that embodies reporting (TABLE), parameters (Amper Variables) and execution flow (Dialogue Manager):

-DEFAULT &COUNTRY = ENGLAND
-IF &COUNTRY EQ '' THEN GOTO NOPARM ;
TABLE FILE CAR 
PRINT COUNTRY CAR
WHERE COUNTRY = '&COUNTRY' ;
END
-EXIT
-NOPARM
TABLE FILE CAR 
PRINT COUNTRY CAR
END
Top of page
x
Procedure: How to Create a Stored Procedure

You can create procedures from the Web Console or the DMC.

  1. From the Web Console menu bar, click Applications, or from the Data Management Console, expand the Server node folder.

    On the Web Console, the Applications page opens.

  2. Right-click an application folder, select New, and then Procedure.

    A text editing window opens.

  3. Type the procedure code, which may be either FOCUS syntax or SQL Passthru syntax. For example: 
    SQL DB2 SELECT x FROM y ;
END

    When using a pure editor environment, you are responsible for the accuracy of the syntax you enter. However, any errors that are generated will be displayed in the right pane, as well as in the Session Log, which you can access from the My Console option on the menu bar.

  4. Click the Run or Save As icon.

    When you Run the procedure, the output is displayed in the right pane.

    When you Save the procedure, the Save As pane opens.

  5. Select an application folder, as show in the following image.

  6. Enter a name in the File Name field.
  7. Click OK.

The procedure is added to the application folder in the navigation pane.

iWay Software