What is Extended Store Procedure ?

What Is Extended Stored Procedures ?

Extended stored procedures let you create your own external routines in a programming language such as C. The extended stored procedures appear to users as regular stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and extended stored procedures can return results and return status.

Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

After an extended stored procedure has been written, members of the sysadmin fixed server role can register the extended stored procedure with the instance of SQL Server, and then grant permission to other users to execute the procedure. Extended stored procedures can be added only to the master database. 

An extended stored procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL stored procedure. Extended stored procedures are a way to extend the capabilities of Transact-SQL to include any resources or services available to Microsoft Win32 applications. In other words you can create a Extended Stored Procedure in your custom language (VC++, VB, Delphi). These dll's can use API's provided by Open Data Services to interact, control and enhance the functionality of SQL server to provide the functionality you might require. 

When to Use Extended Store Procedures ?

Examples:

1. Imagine you have an application that performs some processing on the basis of the some records inserted newly in a particular table. The common way that one may do the same is open a recordset connection on the table and keep polling and re-querying till a new entry is inserted. The drawback in the above example is polling, it would put a lot of burden on the table on frequently accessed tables. The Extended Stored Procedure would adopt an event based approach, where an extended stored procedure residing on the server would be triggered on a new insert and perform custom processing.
2. Imagine you have an application that needs to perform some checks on some condition and immediately send a response mail to someone notifying of the event. The drawback in the above example is you might have a particular condition to trigger the event today and a totally different condition tomorrow. The Extended Stored Procedure would adopt an event-based approach where your custom dll could generate and manage mail, all that one would have to do is to trigger the Extended Stored Procedure in the SQL Server trigger.
3. Implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL.