Remote Procedures

When the proxy table is a procedure-type table, you must provide a column list that matches the description of the remote procedure’s result set. create existing table does not verify the accuracy of this column list.

No indexes are created for procedures.

CIS treats the result set of a remote procedure as a virtual table that can be sorted, joined with other tables, or inserted into another table using insert or select. However, a procedure type table is considered read-only, which means you cannot issue the following commands against the table:

alter table
create index
delete
insert
truncate table
update

Begin the column name with an underscore (_) to specify that the column is not part of the remote procedure’s result set. These columns are referred to as parameter columns. For example:

create existing table rpc1
 (
    a        int,
    b        int,
    c        int,
    _p1      int null,
    _p2      int null
)
external procedure
at "SYBASE.sybsystemprocs.dbo.myproc"

In this example, the parameter columns _p1 and _p2 are input parameters. They are not expected in the result set, but can be referenced in the query:

select a, b, c from t1
where _p1 = 10 and _p2 = 20

CIS passes the search arguments to the remote procedure as parameters, using the names @p1 and @p2.

Parameter-column definitions in a create existing table statement:

Must allow a null value.
Cannot precede regular result columns—they must appear at the end of the column list.

If a parameter column is included in a select list and is passed to the remote procedure as a parameter, the return value is assigned by the where clause.

If a parameter column is included in a select list, but does not appear in the where clause or cannot be passed to the remote procedure as a parameter, its value is NULL.

A parameter column can be passed to a remote procedure as a parameter if the SAP ASE query processor considers it a searchable argument. A parameter column is considered a searchable argument if it is not included in any or predicates. For example, the or predicate in the second line of the following query prevents the parameter columns from being used as parameters:

select a, b, c from t1
where _p1 = 10 or _p2 = 20