Migrating Stored Procedures into application

Question

All of those disadvantages of stored procedures (database portability etc.) I am now facing. We are going to migrate our VB.Net/ASP/SQL Server application to something like Mono/Postgresql and fully internationalize.

One of the many issues we face is that we have 800-900 stored procedures. What we are thinking of doing is moving the logic in these SP's in to the application code. I have searched around for how people have done this or how it can be done however I have found very little. Most of the info on stored procedures is about what should be in them etc. So I have come to the conclusion that the best way to approach this is to leave the SQL retrieve/update type queries as stored procedures and move the ones with business logic in to the application.

So my question is what is the best approach to take in doing something like this (there may not be a best approach but some suggestions of where to start would be good)?

Thanks Liam

Answer 1

Since your stored procedures provide an interface to your database, you now need to ensure that all the calls to the stored procedure are going to the same client code. If you currently call the SP from several places in the application, or even multiple applications (possibly multi-modal), they will all need to go through common code. I would start by ensuring that each SP is called from only one place in your client-side library. Then all that logic from the stored proc is going to be encapsulated by that method call. If your stored procs use transactions to ensure integrity for complex operations, those transactions will now need to be initiated and committed in the application-side library.

That refactoring is ultimately necessary, and there are benefits to that even if you aren't able to port all the SPs.

Answer 2

Hi Liam, I also encountered same situation regarding migrating an ASP.NET+ SQL Server application to Postgres database. Instead of migrating SP to your application code it would be lot easier to convert them to equivalent pl/pgsql functions. All you will need to do it to change the ADO.NET provider and you wont need any change in Application code. There are plenty of providers for postgres (npgsql) which treat SP and postgres functions as equivalent.