Although stored procedures written in the extended SQL dialects of the major enterprise DBMS brands can be quite powerful, they have limitations. One major limitation is that they do not provide access to features outside the DBMS, such as the features of the operating system or other applications running on the same computer system.
The extended SQL dialects also tend to be fairly high-level languages, with limited capability for the lower-level programming usually done in C or C++. To overcome these limitations, some DBMS brands provide access to external stored procedures.
An external stored procedure is a procedure written in a conventional programming language (such as C or Pascal) and compiled outside the DBMS itself. The DBMS is given a definition of the procedure’s name and its parameters, along with other essential information such as the calling conventions used by the programming language in which the stored procedure was written. Once defined to the DBMS, the external stored procedure can be called as if it were a SQL stored procedure. The DBMS handles the
call, turns over control to the external procedure, and then receives any return values and parameters.
Microsoft SQL Server provides a set of system-defined external stored procedures that provide access to selected operating system capabilities. The XP_SENDMAIL procedure can be used to send electronic mail to users, based on conditions within the DBMS:
XP_SENDMAIL ©RECIPIENTS = ‘Joe’, ‘Sam’,
©MESSAGE = ‘Customer table nearly full’;
Similarly, the XP_CMDSHELL external procedure can be called to pass commands to the underlying operating system on which SQL Server is operating. Beyond these predefined external procedures, SQL Server allows a user-written external procedure to be stored in a dynamic-linked library (DLL) and called from within SQL Server stored procedures.
Informix provides basic access to underlying operating system capabilities with a special SYSTEM statement. In addition, it supports user-written external procedures through its CREATE PROCEDURE statement. Where the statement block comprising the body of an Informix SPL procedure would appear, an EXTERNAL clause specifies the name, location, and language of the externally written procedure. With the procedure defined in this way, it can be called in the same way as native Informix SPL procedures. Newer versions of Oracle (Oracle8 and later) provide the same capability, also via the CREATE PROCEDURE statement. IBM’s DB2 database family provides the same set of capabilities.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.