-2

My manager has asked me to "just wrap the stored procedure".

The requirements are to create a microservice that will do the following:

  1. Accept parameters from the user.
  2. Pass those parameters to the stored procedure.
  3. Return to the client the resultset of the stored procedure.

In the beginning of my career this was definitely my approach: tightly coupling everything, hardcoding, no abstractions, etc.

The requirements that I have been given, are to do just this.

Is it possible to tightly-couple several layers, yet still maintain a level of abstraction?

To give slightly more context, the flow is as follows:

Accept user input through an ApiController:

public class MyController : ApiController
{
     public ResponseObject Get ( RequestObject request )
     {
       string sql = GenerateSql(request);
       ResponseObject response = ExecuteSql (sql);
       return response;
     }
}

What's tightly coupled here?

  1. If the stored procedure changes, then the RequestObject needs to change.
  2. If the stored procedure changes, then the ResponseObject needs to change.
  3. If the stored procedure changes, then GenerateSql logic will need to change.
  4. etc

Can I still implement robust design here, given that the time-constraints reflect that everything needs to be hard-coded?

  • How often does the stored procedure change? This should influence your design. – Jon Raynor Nov 15 '17 at 15:55
  • 12
    Argh. Ask your manager what he means. – Robert Harvey Nov 15 '17 at 16:34
  • 3
    Did you just spend more time writing this question than you would have just wrapping the Sproc? – Graham Nov 15 '17 at 18:46
  • In the numbered list at the end, replace "needs to change" with "could possibly need to change" and that describes most systems I've worked with. If the sproc's changing is so detrimental to those other objects, the design of those objects are ill-suited to the task. – Jesse C. Slicer Nov 15 '17 at 18:47
  • 1
    The question is not if you can implement a robust design (of course you can. Everything is possible). The question is if you need it. Given the actual situation and the "complexity" of the "problem". Do you need a sophisticated design? Does such design provides value to the customer/app? In other words. How much decoupling can I afford? Less than need It makes the solution ineficient. More than need It is overegineering. – Laiv Nov 15 '17 at 20:18
4

It seems to come down to this: your manager is happy with the level of abstraction provided by the stored procedure in question. What he wants isn't really another layer in itself--it's just another part of the same layer, providing a syntactically different interface. The question about tightly coupling layers would only be relevant if this really was another layer.

Your question seems predicated upon the idea that another layer providing a higher level of abstraction is really necessary. Without knowing something about the code in question, about all I can do is take a wild guess based on the level of abstraction provided by most other stored procedures I've seen. Based on that, I'd tend to agree that a higher level of abstraction is probably useful and worthwhile (but I doubt anybody can make a statement much stronger than that without more information about the current situation).

On the other hand, I'd have to say that in principle (if only rarely in practice), there's not necessarily anything terribly wrong about the basic idea of doing a basic syntactical translation without attempting to provide a higher level of abstraction. At least in theory, your boss' request could be perfectly reasonable.

2

It isn't a great idea to assemble the SQL in code. It increases the attack surface for injection attacks and it will have slower performance (especially CPU) due to compiling the SQL statement each time.

If you want to link the stored procedure call to the request object, I suggest you set up a component in your build process that emits a SQL deployment script based on the latest request implementation, then deploy the stored procedures along with the code. That way they will always be in sync.

Or you could use a code-first approach; the Entity Framework will generate and deploy the stored procedures for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.