Thoughts On Extended Stored Procedures

Question

I am looking to insert and update records in a database using functions and logic that are not available in SQL Server or any other RDBMS for that matter. After Googling around a bit this morning, I have come across the concept of Extended Stored Procedures. As far as I can tell, I should be able to compile my desired functionality into a dll, make a stored proc utilizing that dll to do the inserting/updating.

However, most of the articles and examples I have come across are somewhat dated (~2000). Are extended stored procedures still an acceptable practice? I am far from an expert in this area, so any other suggestions or comments would be greatly appreciated.

Answer 1

If you're using SQL Server 2005 or later, SQL CLR is the area to look at. You can call .NET code from within SQL Server.

This article on MSDN is a good place to start.

Answer 2

Are extended stored procedures still an acceptable practice?

No, they are officialy deprecated and will be dicontinued in a future release. See Deprecated Database Engine Features in SQL Server 2008 , in the Features Not Supported in a Future Version of SQL Server table:

• Extended stored procedure programming: Use CLR Integration instead.

Answer 3

I usually recommend against using CLR procedures, in most cases you can refactor the problem you are facing, into something that Transact Sql can handle. Of most concern is the procedural approach that often accompanies the use of CLR procedures, when a relation database performs best when performing set based operations.

So the first question I always ask, is there anyway to refactor the problem into a set based operation.

If not, then I ask why would you want to execute the code inside of the database server, instead of in an application layer? Think about the performance impact you might have by placing the logic inside the database. (This might not be an issue if your db server has plenty of extra processing time).

If you do go head with CLR procedures, I think they are best applied to intensive calculations and complex logic.