adding-an-extended-stored-procedure-to-sql-server.md

title	Adding an Extended Stored Procedure to SQL Server | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology
ms.topic	reference
helpviewer_keywords	extended stored procedures [SQL Server], adding adding extended stored procedures collations [SQL Server], extended stored procedures
ms.assetid	10f1bb74-3b43-4efd-b7ab-7a85a8600a50
author	rothja
ms.author	jroth

Adding an Extended Stored Procedure to SQL Server

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

Important

[!INCLUDEssNoteDepFutureDontUse] Use CLR Integration instead.

A DLL that contains extended stored procedure functions acts as an extension to [!INCLUDEssNoVersion]. To install the DLL, copy the file to a directory, such as the one that contains the standard [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] system administrator must register to [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion], but the name will not be usable. For example, although xp_Hello is registered as a [!INCLUDEssNoVersion] extended stored procedure located in xp_hello.dll, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] instance is case-sensitive, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion].

See Also

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