How Add/Drop Extended Store Procedures

Adding an Extended Stored Procedure:

 
To add an extended stored procedure function in an extended stored procedure DLL, you must run the sp_addextendedproc system stored procedure, specifying the name of the function and the name of the DLL in which that function resides. The user executing the command has to be a SQL Server system administrator.
For example, this command registers the function xp_sample, located in xp_sample.dll, as a SQL Server extended stored procedure:

sp_addextendedproc 'xp_sample','xp_sample.dll'

It is a good practice to place the extended stored procedure dll in the SQL Server bin path
along with any dependent dlls. 

SQL Server uses the LoadLibrary() method to locate the dll.

Example :

1. Extract or build the DLL file xp_md5.dll and place it in C:\Program Files\Microsoft SQL Server\MSSQL\Binn (or wherever appropriate). A precompiled DLL is in the Release directory of the source distribution.
2. Create an Extended Stored Procedure called xp_md5 in the "master" database. Right-click "Extended Stored Procedures" under the master database in the Server Manager and click "New Extended Stored Procedure...". Enter xp_md5 for the "Name" and for the "Path", enter the full path to xp_md5.dll. 

USE master;
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'

   3. Create a user-defined function for each database in which you plan to 
use the MD5 procedure. Right-click "User Defined Functions" under the 
appropriate database(s) and click "New User Defined Function...". Enter 
the following:  

 CREATE FUNCTION [dbo].[fn_md5] (@data TEXT) 
RETURNS CHAR(32) AS
BEGIN
  DECLARE @hash CHAR(32)
  EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
  RETURN @hash
END

 

Removing an Extended Stored Procedure:

sp_dropextendedproc 'xp_sample','xp_sample.dll'

How Extended Stored Procedures Work
When a client call (from ISQL, your own application) to the extended stored procedure is performed which in turn executes an extended stored procedure, the request is transmitted to the SQL Server. SQL Server then locates the DLL associated with the extended stored procedure, and loads the DLL if it is not already loaded using the LoadLibrary() method. So inherently the first call to the stored procedure will have the overhead of loading the dll. Then SQL server calls the exported extended stored procedure located in the dll, which gets an interface to the SQL Server in form of a Server Procedure. Your procedure can then read parameters passed to it and return back results if it so intends.
However if your extended stored procedure raises an exception its process is killed and you have to restart the SQL server. SQL server has to be restarted even if you intend to replace the XP dll as it may have been loaded.

Debugging an Extended Stored Procedure
To debug an extended stored procedure DLL by using Microsoft Visual C++ follow the following steps.

1. Stop SQL Server.
2. Copy the updated SQL server extended to SQL Server bin directory preferably.
3. Register the SQL Server using the sp_addextendedproc system stored procedure.
4. In the project settings output Debug category for:
   • Executable for debug session: Provide $MSSSQLSERVERPATH$\BINN\sqlservr.exe
   • Working Directory: Provide $MSSSQLSERVERPATH$\BINN
   • Program arguments: -c so that SQL server starts as an application and not as an service.
5. You can then put your break points and debug as normal.

Note:

1. If you plan to use MFC in the stored procedure or any other dependencies be sure that it is statically linked or the dependency dlls are also available in the $MSSSQLSERVERPATH$\BINN path.
2. Your include directory in options must contain the SQL Development tools include and lib paths.
3. I have tried compiling and checking whether it works on SQL Server 6.5, it does. But I doubt Microsoft supports it for version 6.5.