The execution of an extended stored procedure has these characteristics:
After the extended stored procedure DLL is loaded, the DLL remains loaded in the address space of the server until the SQL Server is stopped or the administrator explicitly unloads the DLL by using DBCC DLL_name (FREE). This is the default behavior.
The extended stored procedure can be executed from Transact-SQL as a stored procedure by using the EXECUTE statement:
EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT
where
Caution Extended stored procedures offer performance enhancements and extend SQL Server functionality. However, because the extended stored procedure DLL and SQL Server share the same address space, a problem procedure can adversely affect SQL Server functioning. Although exceptions thrown by the extended stored procedure DLL are handled by SQL Server, it is possible to damage SQL Server data areas. As a security precaution, only SQL Server system administrators can add extended stored procedures to SQL Server. These procedures should be thoroughly tested before they are installed.