We have some back end processes that runs* on our sql server (SQL Server), they involve processing claims. This requires both data manipulation (biz logic) and data read/write to tables. The biz logic contained should never be used by any of our end user (web/fat client) applications, just for this (runs once, nightly) process.
* By “runs” I mean it runs, but it is slow, full or errors, and is a nightmare to maintain, we’re looking to redo this to fix these issues.
One thing I’m trying to avoid is writing a 3000+ line Stored Proc (we have a few) that does everything, it isn’t maintainable.
My initial thoughts/approach is to use:
- SQL CLR (Sql 2012 / C# .Net 4.0) for the biz logic (there will be some complexity to the logic)
- Entity Framework for any simple CRUD read/write
- Stored Procedures for any complex data read/writes.
There will be a final step of writing our end results (data) to other companies databases, but that’s someone else responsibility.
I’d like some thoughts about this approach if it is a good one (is there a better approach?), any suggestions would be appreciated.
Since you don’t really need to run this “in” SQL Server and you don’t need to reuse any of the code anywhere else in SQL Server, create another server application in the language of your choice and have it triggered by a scheduler. This could give you the option to off-load the number crunching on another server. Using stored procs are still an option if you need it.
You may not have CLR enabled, so why bother just for this?
3
Personally, I prefer only using sp’s when the process is only at the database level, e.g. populating some tables based on values in other tables, such as doing monthly data updates, etc. If for no other reason, it’s difficult (at least in my experience) to properly version sp’s, so I like to leave out any business logic from the database if at all possible.
If the process can be completed in the database, stick to stored procedures as these should perform best.
You are right in trying to avoid a procedure that contains many lines of code. If this is the case then split the procedure up into a number of smaller procedures. There may be commonality between these, so you can introduce re-use with the use of variables passed to your procedures. How you call these will depend on the system design – you may need a Business layer in the application or a master stored procedure to do this.