Example of creating and using ESPs

After an ESP function has been written, compiled, and linked into a DLL, you can create an ESP for the function using the as external name clause of the create procedure command:

create procedure procedure_name [parameter_list]
     as external name dll_name

procedure_name is the name of the ESP, which must be the same as the name of its implementing function in the DLL. ESPs are database objects, and their names must follow the rules for identifiers.

dll_name is the name of the DLL in which the implementing function is stored.

The following statement creates an ESP named getmsgs, which is in msgs.dll. The getmsgs ESP takes no parameters. This example is for a Windows Adaptive Server:

create procedure getmsgs 
as external name "msgs.dll"

On a Solaris Adaptive Server, the statement is:

create procedure getmsgs 
as external name "msgs.so"

This reflects the Solaris naming conventions.

The next statement creates an ESP named getonemsg, which is also in msgs.dll. The getonemsg ESP takes a message number as a single parameter.

create procedure getonemsg @msg int
as external name "msgs.dll"

When Adaptive Server creates an ESP, it stores the procedure’s name in the sysobjects system table, with an object type of “XP” and the name of the DLL containing the ESP’s function in the text column of the syscomments system table.

Execute an ESP as if it were a user-defined stored procedure or system procedure. You can use the keyword execute and the name of the stored procedure, or just give the procedure’s name, as long as it is submitted to Adaptive Server by itself or is the first statement in a batch. For example, you can execute getmsgs in any of these ways:

getmsgs
execute getmsgs
exec getmsgs

You can execute getonemsg in any of these ways:

getonemsg 20
getonemsg @msg=20
execute getonemsg 20
execute getonemsg @msg=20
exec getonemsg 20
exec getonemsg @msg=20