Extended Stored Procedures

What is a stored procedure?

A stored procedure is a piece of code written in TSQL that performs one or more operations. The code may be a simple TSQL statement or batch, or may have procedural workflow elements. Stored procedures are called with the exec statement and may pass one or more parameters. Stored procedures may be called from applications, by triggers or by other stored procedures.

e.g.

CREATE PROC SP_MYPROC (@VCHARIN varchar(20),@VCHAROUT varchar(20)
as
	begin
		set nocount on
		select @VCHAROUT = @VCHARIN
	end
GO
EXEC SP_MYPROC "Hello World",@VCHAROUT OUTPUT

What is an extended stored procedure?

An extended stored procedure (XP) has the same interface as a stored procedure but has been compiled from a language such as C or C++.

An XP is provided in the form of what I would call a "traditional" DLL i.e. not a COM object. The DLL may contain one or more XPs and these are recorded in SQLServer's master database by running a the stored procedure sp_addextendedproc

e.g.

exec sp_addextendedproc 'xp_msw_datestamp', 'xpmswfiles.dll'
GRANT EXEC ON xp_msw_datestamp TO System

Microsoft provide some of their own XPs to provide functionality such as security, interaction with command line applications and creating COM objects

Data can be passed to the extended stored procedure in the form of parameters or via a loopback connection that reads the data tables using ODBC or equialent

The API used for extended stored procedures was origionally a sub-set of the Open Data Services (ODS) however this API has been recently renamed to be the "Extended Stored Procedure API" as Microsoft has phased out the need for ODS. Many of the applications that would have previously needed ODS can now use remote data sources instead via OPENQuery and linked servers.

Why would you create your own extended stored procedure?

As Microsoft adds more functionality into the different versions of SQL Server, there becomes less reason for people to create their own extended stored procedures. My own thoughts are that there are still some niche areas where this functionality comes into it's own. The two areas most suited to XPs are intensive computation and accessing external resources.

XPs are suited for intensive computation because they allow the developer to craft very optimal code into a compiled DLL and pass it back to SQL Server in the form of a OUTPUT variable or record set. Microsoft's examples, XP_ECHO and XP_PROCLIST show examples of these two methods.

XPs can often be the only way to access external resources. SQL Server is designed to isolate you from the operating system but there are times that you need to access external information in your applications. For example you may need to interface to external hardware and software via an API. XP_DISKLIST and XP_DISKFREE are examples of this.

In the case of my photo replication system, I needed to time stamp files on the hard disk, access a sub-set of files from a folder and upload binary files in the form of text. Three XPs were created to meet these functions, and a interface class was used to simplify the coding.

And why not?

In the recent versions of SQL Server, it is possible to run VBScript commands as a scheduled job. This can provide a solution to some of situations where external resources need to be accessed. There are also many XPs provided for you with features such as navigating folders and checking for the existance of files.

An extended stored procedure runs in the memory space of SQL Server, this means that it is typically very fast but if it causes an exception then your whole database server could be brought down.

The future for extended stored procedures?

In the next version of SQL Server, languages other than TSQL can be used in stored procedures. These will utilise the CLR to keep the code "safe" this may eliminate the need for extended stored procedures all together.

It is fairly clear that Microsoft will promote the phasing out of extended stored procedures in favour of CLR based stored procedures.

Where next?

• I recommend looking at the example provided with the developer edition of SQL Server and with the various platform SDKs.
• Read the Microsoft Articles on debugging extended stored procedures and "All about Extended Stored Procedures"
• Read the articles on the problem Microsoft has fixed to do with handling of parameters.
• When writing the XPs try to clearly isolate the functions of handling and validating parameters, core processing and returning the results. This will simplify debugging/maintenance and allow for better code reuse.
• Add a version function into your XP, this is a good practice exercise and will allow you to check for the correct version in your TSQL or application.
• Be aware that your DLL needs to be thread safe and re-entrant. SQL Server is frequently deployed on SMP machines.

Feel free to send an email if you have a specific question.