SQL Server stored procedure question

Question

I have a SQL Server stored procedure which has been in use for years. This stored procedure calls lots of other procedures. I would like to extract each inside procedure one at a time and implement its business logic to a .NET Class project.

In order to do that, I have to call .NET assembly from parent stored procedure and the returned result will be used by parent procedure. Since SQL Server 2005 and higher has CLR integration, so I think, executing .NET assembly inside stored procedure [or any Database objects] should not be a big deal, can you please point me some references where i can find examples or article to implement it?

Thank you very much for your help .

Answer 1

MSDN
Building my First SQL Server 2005 CLR
An Intro to CLR Integration in SQL Server 2005
For loads more ;)

Answer 2

I really feel that this would be an inappropriate use of SQL CLR. The purpose of CLR integration is to support complex data types and operations that are normally very hard to do in pure SQL (such as sequences, regular expressions, hierarchy, geospatial, etc.) Not to implement a domain model in your database.

Domain models and business logic are separate from relational/data models. They should be in a proper business tier of some sort. Don't hack them into a database using the CLR.

(Note: I use SQLCLR a fair bit. I am not railing on CLR integration. I just don't think that this question reflects a wise design decision.)

Answer 3

I think you should use SQL Server Integration Services (SSIS). As far as I understand, it solves this case, to orchestrate the procedure calls and gives you much more too..

I'm not too sure if moving this decision outside the db layer is a good decision.

Hope it helps..