What does it mean to "wrap" a stored procedure?

Question

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?

Answer 1

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.

Answer 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.