Extended Stored Procedures Usage

Extended stored procedures (ESPs) provide a mechanism for calling external procedural language functions from within SAP ASE. Users invoke ESPs using the same syntax as stored procedures; the difference is that an ESP executes procedural language code rather than Transact-SQL statements.

Each ESP is associated with a corresponding function, which is executed when the ESP is invoked from SAP ASE.

An ESP allows SAP ASE to perform a task outside SAP ASE in response to an event occurring within SAP ASE. For example, you could create an ESP function to sell a security. This ESP is invoked in response to a trigger, fired when the price of the security reaches a certain value. Or you could create an ESP function that sends an e-mail notification or a network-wide broadcast in response to an event occurring within the relational database system.

For the purposes of ESPs, “a procedural language” is a programming language that is capable of calling a C language function and manipulating C-language datatypes.

After a function has been registered in a database as an ESP, it can be invoked just like a stored procedure from isql, from a trigger, from another stored procedure, or from a client application.

SAP ASE supplies some system ESPs. For example, one system ESP, xp_cmdshell, executes an operating system command from within SAP ASE. You can also write your own ESPs using a subset of the Open Server application programming interface (API).

• XP Server
  Extended stored procedures are implemented by an Open Server application called XP Server, which runs on the same machine as SAP ASE. SAP ASE and XP Server communicate through remote procedure calls (RPCs).
• Dynamic Link Library Support
  The procedural functions that contain the ESP code are compiled and linked into dynamic link libraries (DLLs), which are loaded into XP Server memory in response to an ESP execution request.
• Open Server API
  SAP ASE uses the Open Server API, which allows users to run the system ESPs provided with SAP ASE. Users can also use the Open Server API to implement their own ESPs.
• ESPs and Permissions
  You can grant and revoke permissions on an ESP as you would on a regular stored procedure.
• ESPs and Performance
  Since both SAP ASE and XP Server reside on the same machine, they can affect each other’s performance when XP Server is executing a function that consumes significant resources.
• Create Functions for ESPs
  A function that implements an ESP must be written in a procedural programming language that is capable of calling a C-language function, manipulating C-language datatypes, and linking with the Open Server API. .
• Registering ESPs
  Once you have created an ESP function and linked it into a DLL, register it as an ESP in a database, which lets users execute the function as an ESP.
• Remove ESPs
  To remove an ESP from the database, use either drop procedure or sp_dropextendedproc.
• Execute ESPs
  Execute an ESP using the same execute command that you use to execute a regular stored procedure.
• System ESPs
  In addition to xp_cmdshell, there are several system ESPs that support Windows features, such as the integration of SAP ASE with the Windows Event Log or mail system.
• Get Information About ESPs
  Use sp_helpextendedproc to get information about ESPs that are registered in the current database.
• ESP Exceptions and Messages
  SAP ASE handles all messages and exceptions from XP Server. It logs standard ESP messages in the log file in addition to sending them to the client. User-defined messages from user-defined ESPs are not logged, but they are sent to the client.