How to avoid backend stored procedures
-
In the team I am currently working with, we have to deal with a software which was initially thought as a database driven programme. As a result of that, there is a set of Databases, having each more or less 300 stored procedures! We cannot anymore accept writing backend stored procedures, as they do not contain any object oriented thinking and in the end we have a procedural code like in the days of old good C. We have to deal with lots of bugs, which is not sure whether they come from backend (MSSQL) or the frontend (Windows App in C#) Are there any other solutions to avoid so much SPs and still have a professional code? I thought of exchanging SELECT SPs through Views but I am interested on what other ways do exist. Thank you.
-
In the team I am currently working with, we have to deal with a software which was initially thought as a database driven programme. As a result of that, there is a set of Databases, having each more or less 300 stored procedures! We cannot anymore accept writing backend stored procedures, as they do not contain any object oriented thinking and in the end we have a procedural code like in the days of old good C. We have to deal with lots of bugs, which is not sure whether they come from backend (MSSQL) or the frontend (Windows App in C#) Are there any other solutions to avoid so much SPs and still have a professional code? I thought of exchanging SELECT SPs through Views but I am interested on what other ways do exist. Thank you.
Why only backend stored procs? I would run away from any frontend stored procs first. :laugh:
No object is so beautiful that, under certain conditions, it will not look ugly. - Oscar Wilde
-
In the team I am currently working with, we have to deal with a software which was initially thought as a database driven programme. As a result of that, there is a set of Databases, having each more or less 300 stored procedures! We cannot anymore accept writing backend stored procedures, as they do not contain any object oriented thinking and in the end we have a procedural code like in the days of old good C. We have to deal with lots of bugs, which is not sure whether they come from backend (MSSQL) or the frontend (Windows App in C#) Are there any other solutions to avoid so much SPs and still have a professional code? I thought of exchanging SELECT SPs through Views but I am interested on what other ways do exist. Thank you.
nstk wrote:
having each more or less 300 stored procedures!
..it can hold lots more.
nstk wrote:
as they do not contain any object oriented thinking
And why is that a problem?
nstk wrote:
in the end we have a procedural code like in the days of old good C.
C and TSQL are different beasts.
nstk wrote:
We have to deal with lots of bugs, which is not sure whether they come from backend (MSSQL) or the frontend (Windows App in C#)
Simple; if it is raised after executing a query, it is in the database. With sprocs it is even easier to verify as one only has to check the parameters. A good way would be to have error-handling in the sproc, and rollback any changes if there's an error, and raising your own custom error that gets handled and logged by the C# code. There's examples on this site on how to do so.
nstk wrote:
Are there any other solutions to avoid so much SPs and still have a professional code?
Yes, the alternative is to use inline SQL. That'll be even harder to debug.
nstk wrote:
I thought of exchanging SELECT SPs through Views
A view is nothing more than a SELECT statement. You can create 300 views, but they would not be very object-oriented. You also cannot pass a parameter to a view like one could with a sproc. Finally you could execute the select directly from code, without any views or sprocs - but that would be rather dirty. SProcs are the professional way.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
In the team I am currently working with, we have to deal with a software which was initially thought as a database driven programme. As a result of that, there is a set of Databases, having each more or less 300 stored procedures! We cannot anymore accept writing backend stored procedures, as they do not contain any object oriented thinking and in the end we have a procedural code like in the days of old good C. We have to deal with lots of bugs, which is not sure whether they come from backend (MSSQL) or the frontend (Windows App in C#) Are there any other solutions to avoid so much SPs and still have a professional code? I thought of exchanging SELECT SPs through Views but I am interested on what other ways do exist. Thank you.
I can't see what problem you're trying to solve, other than to arbitrarily re-factor your application's architecture. Do you have a specific issue with these backend stored procedures? from what you have described here, I can't see any problem. Database servers can easily handle hundreds of stored procedures. There are also huge benefits to using stored procedures such as performance and load balancing. If you are experiencing specific problems then please describe what they are.
"There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult." - C.A.R. Hoare Home | LinkedIn | Google+ | Twitter