Extended Stored Procedures

for RuBoard

Extended procedures are routines residing in DLLs that function similarly to regular stored procedures. They receive parameters and return results via SQL Server's Open Data Services API and are usually written in C or C++. They must reside in the master database and run within the SQL Server process space.

Although the two are similar, calls to extended procedures work a bit differently than calls to system procedures. Extended procedures aren't automatically located in the master database and they don't assume the context of the current database when executed. To execute an extended procedure from a database other than the master, you have to fully qualify the reference (e.g., EXEC master.dbo.xp_cmdshell 'dir').

A technique for working around these differences is to "wrap" an extended procedure in a system stored procedure. This allows it to be called from any database without requiring the master prefix. This technique is used with a number of SQL Server's own extended procedures. Many of them are wrapped in system stored procedures that have no purpose other than to make the extended procedures they call a bit handier. Listing 1-19 is an example of a system procedure wrapping a call to an extended procedure:

Listing 1-19 System procedures are commonly used to "wrap" extended procedures.

 USE master IF (OBJECT_ID('dbo.sp_hexstring') IS NOT NULL)   DROP PROC dbo.sp_hexstring GO CREATE PROC dbo.sp_hexstring @int varchar(10)=NULL, @hexstring varchar(30)=NULL OUT /* Object: sp_hexstring Description: Return an integer as a hexadecimal string Usage: sp_hexstring @int=Integer to convert, @hexstring=OUTPUT parm to receive hex string Returns: (None) $Author: Khen $. Email: [e-mail] $Revision: 1 $ Example: sp_hexstring "23", @myhex OUT Created: 1999-08-02. $Modtime: 1/4/01 8:23p $. */ AS IF (@int IS NULL) OR (@int = '/?') GOTO Help DECLARE @i int, @vb varbinary(30) SELECT @i=CAST(@int as int), @vb=CAST(@i as varbinary) EXEC master.dbo.xp_varbintohexstr @vb, @hexstring OUT RETURN 0 Help: EXEC sp_usage @objectname='sp_hexstring',        @desc='Return an integer as a hexadecimal string',        @parameters='@int=Integer to convert, @hexstring=OUTPUT parm to receive hex string',        @example='sp_hexstring "23", @myhex OUT',        @author='Ken Henderson',        @email='[e-mail]',        @version='1', @revision='0',        @datecreated='19990802', @datelastchanged='19990815' RETURN -1 GO DECLARE @hex varchar(30) EXEC sp_hexstring 10, @hex OUT SELECT @hex 

(Results)

 ------------------------------ 0x0000000A 

The whole purpose of sp_hexstring is to clean up the parameters to be passed to the extended procedure xp_varbintohexstr before calling it. Because sp_hexstring is a system procedure, it can be called from any database without requiring the caller to reference xp_varbintohexstr directly.

Internal Procedures

A number of system-supplied stored procedures are neither true system procedures nor extended proceduresthey're implemented internally by SQL Server. Examples of these include sp_executesql, sp_xml_preparedocument, most of the sp_cursor routines, sp_reset_connection, and so forth. These routines have stubs in master..sysobjects, and are listed as extended procedures, but they are actually implemented internally by the server, not within an external ODS-based DLL. This is important to know because you cannot drop these or replace them with updated DLLs. They can be replaced only by patching SQL Server itself, which normally only happens when you apply a service pack.

for RuBoard