Creating remote procedures (SQL)

You can create remote procedures and functions in Interactive SQL.

Prérequis

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.

Contexte et remarques

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.

Résultat

The remote procedure or function is created.

Exemple

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