An optional clause may be added to the create existing table statement to indicate the remote object is actually a stored (or other) procedure instead of a table. Without this clause, the remote object is assumed to be a table or view:
create existing table t1 ( column_1 int, column_2 int ) EXTERNAL PROCEDURE AT "SERVER_A.mydb.dbo.p1"
In the case where the remote object is type procedure, several processing differences occur:
No indexes are created for objects of this type.
A column list must be provided which matches the description of the remote procedure’s result set. This column list is the responsibility of the user, and no verification of its accuracy is provided.
Column names beginning with underscore (‘_’) can be used to specify parameters, which are 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” select a, b, c from t1 where _p1 = 10 and _p2 = 20
In this example, the parameter columns _p1 and _p2 are not expected in the result set, but can be referenced in the query. CIS passes the search arguments to the remote procedure via parameters, using the names @p1 and @p2.
If a parameter column is included in the select list, its value is equivalent to the values specified for it in the where clause, if it was passed to the remote procedure as a parameter. If the parameter column did not appear in the where clause, or was not able to be passed to the remote procedure as a parameter, but was included in the select list, its value would be NULL.
A parameter column can be passed to the remote procedure as a parameter if it is what the Adaptive Server Enterprise query processor considers to be a searchable argument, or SARG. It is generally a SARG if it is not included in any or predicates. For example, the following query would prevent the parameter columns from being used as parameters:
select a, b, c from t1 where _p1 = 10 OR _p2 = 20
Rules exist for the definition of parameter columns in the create existing table statement:
parameter columns must allow NULL.
parameter columns cannot precede normal, result columns (i.e. they must appear at the end of the column list).
Allowing the definition of remote procedures as local tables gives CIS the ability to treat the result set of a remote procedure as a ‘virtual table,’ which can be sorted, joined with other tables, or inserted into another table via insert/select syntax. However, tables of this type are considered read only:
You cannot issue a delete, update or insert command against a table of type procedure;
You cannot issue a create index, truncate table or alter table command against tables of this type.
Component Integration Services users can map remote or external objects of the type rpc to local proxy tables. If a table is created in this way, it can be referenced only by the select and drop commands. The commands insert, delete, and update generate error messages, since the table is assumed to be read-only. Proxy definitions should only be created for procedures which return data.
If an object of the type rpc has been defined within the server, a query is not issued to the remote server on which the object resides. Instead, the server issues an RPC and treats the results from the RPC as a read-only table.
create existing table rtable ( col1 int, col2 datetime, col3 varchar(30) ) external procedure at “RMTSERVER...myproc “
select * from rtable
When this query is issued, the server sends the RPC named myproc to server RMTSERVER. Row results are treated like the results from any other table; they can be sorted, joined with other tables, grouped, inserted into another table, and so forth.
RPC parameters should represent arguments that restrict the result set. If the RPC is issued without parameters, the entire result set of the object is returned. If the RPC is issued with parameters, each parameter further limits the result set. For example, the following query:
select * from rtable where col1 = 10
results in a single parameter, named @col1, that is sent along with the RPC. Its value is 10.
Component Integration Services attempts to pass as many of the search arguments as possible to the remote server, but depending on the SQL statement being executed, Component Integration Services might perform the result set calculation itself. Each parameter represents a search for an exact match, for example, the = operator.
The following are rules which define the parameters sent to the RPC. If an RPC is used as a Component Integration Services object, these rules should be kept in mind during development.
Component Integration Services sends = operators in the where clause as parameters. For example, the query:
select * from rpc1 where a = 3 and b = 2
results in Component Integration Services sending two parameters. Parameter a has a value of 3 and parameter b has a value of 2. The RPC is expected to return only result rows in which column a has a value of 3 and column b has a value of 2.
Component Integration Services does not send any parameters for a where clause, or portion of a where clause, if there is not an exact search condition. For example:
select * from rpc1 where a = 3 or b = 2
Component Integration Services does not send parameters for a or b because of the or clause.
Another example:
select * from rpc1 where a = 2 and b < 3
Component Integration Services does not send parameters because there is nothing in the where clause representing an exact search condition. Component Integration Services performs the result set calculation locally.
