Using create procedure

The create procedure syntax is:

create procedure [owner.]procedure_name 
     [[(]@parameter_name datatype [= default] [output] 
     [, @parameter_name datatype [= default] 
     [output]]...[)]] [with recompile] 
     as external name dll_name

procedure_name is the name of the ESP as it is known in the database. It must be the same as the name of its supporting function.

The parameter declarations are the same as for stored procedures, as described in Chapter 17, “Using Stored Procedures.”

Adaptive Server ignores the with recompile clause if it is included in a create procedure command used to create an ESP.

The dll_name is the name of the library containing the ESP’s supporting function. Specify it as a name with no extension (for example, msgs), or as a name with a platform-specific extension, such as msgs.dll on Windows or msgs.so on Solaris. In either case, the platform-specific extension is assumed to be part of the library’s actual file name.

Since create procedure registers an ESP in a specific database, specify the database in which you are registering the ESP before invoking the command. From isql, specify the database with the use database command, if you are not already working in the target database.

The following statements register an ESP supported by the xp_echo routine described in the example in “ESP function example”, assuming that the function is compiled in a DLL named examples.dll. The ESP is registered in the pubs2 database.

use pubs2
create procedure xp_echo @in varchar(255)
as external name "examples.dll"

See the Reference Manual: Commands.