Adding an Extended Stored Procedure to SQL Server

Applies to: SQL Server

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.

A DLL that contains extended stored procedure functions acts as an extension to SQL Server. To install the DLL, copy the file to a directory, such as the one that contains the standard SQL Server DLL files (C:\Program Files\Microsoft SQL Server\MSSQL12.0.x\MSSQL\Binn by default).

After the extended stored procedure DLL has been copied to the server, a SQL Server system administrator must register to SQL Server each extended stored procedure function in the DLL. This is done using the sp_addextendedproc system stored procedure.

Important

The system administrator should thoroughly review an extended stored procedure to ensure that it does not contain harmful or malicious code before adding it to the server and granting execute permissions to other users. Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input.

The first parameter of sp_addextendedproc specifies the name of the function, and the second parameter specifies the name of the DLL in which that function resides. It is recommended that you specify the complete path of the DLL.

Important

Existing DLLs that were not registered with a complete path will not work after upgrading to SQL Server 2005 or later. To correct the problem, use sp_dropextendedproc to unregister the DLL, and then reregister it with sp_addextendedproc, specifying the complete path.

The name of the function specified in sp_addextendedproc must be exactly the same, including the case, as the function's name in the DLL. For example, this command registers a function xp_hello, located in a dll named xp_hello.dll, as a SQL Server extended stored procedure:

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';  

If the name of the function specified in sp_addextendedproc does not exactly match the function name in the DLL, the new name will be registered in SQL Server, but the name will not be usable. For example, although xp_Hello is registered as a SQL Server extended stored procedure located in xp_hello.dll, SQL Server will not be able to find the function in the DLL if you use xp_Hello to call the function later.

--Register the function (xp_hello) with an initial upper case  
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';  
  
--Use the newly registered name to call the function  
DECLARE @txt varchar(33);  
EXEC xp_Hello @txt OUTPUT;  
  
--This is the error message  
Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1  
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).  

If the name of the function specified in sp_addextendedproc matches exactly the function name in the DLL, and the collation of the SQL Server instance is case-insensitive, the user can call the extended stored procedure using any combination of lower- and upper-case letters of the name.

--Register the function (xp_hello)  
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';  
  
--The following will succeed in calling xp_hello  
DECLARE @txt varchar(33);  
EXEC xp_Hello @txt OUTPUT;  
  
DECLARE @txt varchar(33);  
EXEC xp_HelLO @txt OUTPUT;  
  
DECLARE @txt varchar(33);  
EXEC xp_HELLO @txt OUTPUT;  

When the collation of the SQL Server instance is case-sensitive, SQL Server will not be able to call the extended stored procedure -- even if it was registered with exactly the same name and collation as the function in the DLL -- if the procedure is called with a different case.

--Register the function (xp_hello)  
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';  
  
--The following will result in an error  
DECLARE @txt varchar(33);  
EXEC xp_HELLO @txt OUTPUT;  
  
--This is the error  
Server: Msg 2812, Level 16, State 62, Line 1  

It is not necessary to stop and restart SQL Server.

See Also

sp_addextendedproc (Transact-SQL)
sp_dropextendedproc (Transact-SQL)