Extended Stored Procedures (From C, C++ DLL)

There are three types of stored procedures in SQL Server.
1. Standards Stored Procedures
2. CLR Stored Procedures
3. Extended Stored Procedures

Today we are going to discuss about Extended Stored Procedures. If you are new to SQL Server world (just a few years old in SQL Server environment) then it might be the first time when you have ever heard of this name. So let’s take a look what they are:



There are sometimes requirement to perform operations which are very difficult or impossible to do in SQL e.g. WIN32 operations like killing a system process, writing to the event log, encrypting data to a database, parsing XML, shelling to the command-line. Back in earlier days, we needed something to support such operations. For these types of operations Extended Stored Procedures were introduced in SQL Server earlier. Now you would say: what is its requirement now, when you can write these operations in managed code using CLR Stored Procedures? But just wait a moment and believe that you are living in a world of SQL Server 7. There is no .net framework yet developed. Now answer how would you do this? Your answer must be using C++ DLLs. Yes! This is the same that Extended Procedures were introduced for.

In SQL Server 2005, even with introduction of CLR Stored Procedures, extended stored procedures remained part of the DBMS. But because of this there always existed a backdoor for hackers into your server because these stored procedures were basically unmanaged code and had known issue of Buffer Overflow.


It is good to know that these stored procedures will no more be part of a new version of SQL Server. All of you, who are planning to move to next version of SQL Server after 2008 version, make this part of plan to rewrite all your extended stored procedures in a CLR compliant language. At least what we can do is that we don’t develop any new logic using extended stored procedures and keep porting our earlier extended stored procedures to CLR.

Enjoy!