Reporting Against a Sybase Stored Procedure

In this section:

You can use a reporting tool, such as a SELECT statement or TABLE command, to execute Sybase stored procedures and report against a procedure's output parameters and answer set. Among the benefits of this method of executing a stored procedure are:

To report against a stored procedure:

  1. Generate a synonym for the stored procedure answer set, as described in Generating a Synonym for a Stored Procedure.
  2. Create a report procedure, as described in Creating a Report Against a Stored Procedure.
  3. Run the report. This executes the stored procedure and reports against any output parameters (OUT and INOUT in OUT mode), and any answer set fields, specified in the report.

Note: For Sybase IQ data, support for stored procedures began with the SYBIQ 12.7 ESD #4 release.


Top of page

x
Generating a Synonym for a Stored Procedure

Reference:

A synonym describes a stored procedure's parameters and answer set.

An answer set's structure may vary depending on the input parameter values that are provided when the procedure is executed. Therefore, you need to generate a separate synonym for each set of input parameter values that will be provided when the procedure is executed at run time. For example, if users may execute the stored procedure using three different sets of input parameter values, you need to generate three synonyms, one for each set of values. (Unless noted otherwise, "input parameters" refers to IN parameters and to INOUT parameters in IN mode.)

There is an exception: if you know the internal logic of the procedure, and are certain which range of input parameter values will generate each answer set structure returned by the procedure, you can create one synonym for each answer set structure, and for each synonym simply provide a representative set of the input parameter values necessary to return that answer set structure.

A synonym includes the following segments:



x
Reference: Synonym Creation Parameters for Stored Procedures

Parameter/Task

Description

Restrict Object Type to

Select Stored Procedures.

Database selection

To specify a database from which you can select a table or other object, do one of the following:

  • Check Use current database to use the database that has been set as the default database.
  • Select a database from the Select database drop-down list, which lists all databases in the current DBMS instance.

    Before selecting a database, if Use current database is checked, uncheck it.

This option applies to Sybase ASE. It does not apply to Sybase IQ.

Filter by Owner/Schema and Object name

Selecting this option adds the Owner/Schema and Object Name parameters to the screen.

Owner/Schema. Type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select tables or views whose owner/schema begin with the letters ABC; %ABC to select tables or views whose owner/schema end with the letters ABC; %ABC% to select tables or views whose owner/schema contain the letters ABC at the beginning, middle, or end.

Object name. Type a string for filtering the procedure names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all procedures whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

Select

Select a procedure. You may only select one procedure at a time since each procedure will require unique input in the Values box on the next synonym creation pane.

Name

The name of the synonym, which defaults to the stored procedure name.

Select Application

Select an application directory. The default value is baseapp.

Prefix/Suffix

If you have stored procedures with identical names, assign a prefix or a suffix to distinguish their corresponding synonyms. Note that the resulting synonym name cannot exceed 64 characters.

If all procedures have unique names, leave the prefix and suffix fields blank.

Overwrite Existing Synonyms

To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box.

Customize data type mappings

To change the data type mappings from their default settings, select this check box. The customizable mappings are displayed. For information about them, see Data Type Support in the chapter for your adapter in the Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS manual.

Values

Select the check box for every parameter displayed for the specified procedure.

Note the following before you enter parameter values: if the procedure you selected has input parameters (IN parameters and/or INOUT parameters in IN mode), you will be prompted to enter values for them. However, the need for an explicit Value entry depends on the logic of the procedure and the data structures it produces. Therefore, while you must check the parameter box, you may not need to enter a value. Follow these guidelines:

  • Explicit input values (and separate synonyms) are required when input parameter values cause answer sets with different data structures, which vary depending on the input parameters provided.
  • Explicit input values are not required when you know the procedure's internal logic and are certain that it always produces the same data structure. In this situation, only one synonym needs to be created and you can leave the Value input blank for synonym-creation purposes.

If a Value is required, enter it without quotes. Any date, date-time, and timestamp parameters must have values entered in an ISO format. Specify the same input parameters that will be provided when the procedure is executed at run time if it is a procedure that requires explicit values.



x
Creating a Report Against a Stored Procedure

How to:

You can report against a stored procedure answer set using the same facilities you use to report against a database table:

When joining from or to a stored procedure answer set, you can:



x
Syntax: How to Report Against a Stored Procedure Using the TABLE Command

To execute a stored procedure using the TABLE command, use the following syntax

TABLE FILE synonym 
PRINT [parameter [parameter] ... | *]
[IF in-parameter EQ value]
  .
  .
  .
END

where:

synonym

Is the synonym of the stored procedure you want to execute.

parameter

Is the name of a parameter whose values you want to display in the report. You can specify input parameters, output parameters, or input and output parameters.

If the stored procedure does not require parameters, specify an asterisk (*). This displays a dummy segment, created when the synonym is generated, to satisfy the structure of the SELECT statement.

*

Indicates that you want to display all indicated parameters, or that there are no required parameters.

IF

Is an IF or WHERE keyword. Use this to pass a value to an IN parameter or an INOUT parameter in IN mode.

in-parameter

Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.

Note: The length of in-parameters cannot exceed 1000 characters if the adapter is configured for Unicode support.

value

Is the value you are passing to a parameter.



x
Syntax: How to Report Against a Stored Procedure Using SELECT
SQL
SELECT [parameter [,parameter] ... | *] FROM synonym 
[WHERE in-parameter = value]
  .
  .
  .
END

where:

synonym

Is the synonym of the stored procedure that you want to execute.

parameter

Is the name of a parameter whose values you want to display in the report. You can specify input parameters, output parameters, or input and output parameters.

If the stored procedure does not require parameters, enter an asterisk (*) in the syntax. This displays a dummy segment, created during synonym generation, to satisfy the structure of the SELECT statement.

*

Indicates that you want to display all indicated parameters, or that there are no required parameters.

WHERE

Is used to pass a value to an IN parameter or an INOUT parameter in IN mode.

You must specify the value of each parameter on a separate line.

in-parameter

Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.

value

Is the value you are passing to a parameter.


iWay Software