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:
Note: For Sybase IQ data, support for stored procedures began with the SYBIQ 12.7 ESD #4 release.
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:
If there are no IN parameters or INOUT parameters in IN mode, the segment describes a single dummy field.
If there are no OUT parameters or INOUT parameters in OUT mode, the segment is omitted.
If there is no answer set, the segment is omitted.
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:
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:
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. |
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:
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:
Is the synonym of the stored procedure you want to execute.
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.
Is an IF or WHERE keyword. Use this to pass a value to an IN parameter or an INOUT parameter in IN mode.
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.
Is the value you are passing to a parameter.
SQL SELECT [parameter [,parameter] ... | *] FROM synonym [WHERE in-parameter = value] . . . END
where:
Is the synonym of the stored procedure that you want to execute.
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.
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.
Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.
Is the value you are passing to a parameter.
iWay Software |