Creating remote procedures (SQL)

You can create remote procedures and functions in Interactive SQL.

Prerequisites

You must have the CREATE PROCEDURE system privilege to create procedures and functions owned by you. You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT privilege to create procedures and functions owned by others. To create external procedures and functions, you must also have the CREATE EXTERNAL REFERENCE system privilege.

Context and remarks

If a remote procedure can return a result set, even if it does not always return one, then the local procedure definition must contain a RESULT clause.

Create a remote procedure

Connect to the host database.

Execute a statement to define the procedure or function.

For example:

CREATE PROCEDURE RemoteProc()
AT 'bostonase.master.dbo.sp_proc';

CREATE FUNCTION RemoteFunc()
RETURNS INTEGER
AT 'bostonasa..dbo.sp_func';

The syntax is similar to a local procedure definition. The location string defines the location of the procedure.

Results

The remote procedure or function is created.

Example

This example specifies a parameter when calling a remote procedure:

CREATE PROCEDURE RemoteUser ( IN username CHAR( 30 ) )
AT 'bostonase.master.dbo.sp_helpuser';
CALL RemoteUser( 'joe' );

This example creates an interface to a function defined at the remote server RemoteSA:

CREATE FUNCTION proxy_maxorder()
RETURNS INTEGER
AT 'RemoteSA;;DBA;maxorder';

Creating remote procedures (SQL)

Create a remote procedure

See also