tip 1 : it’s a wrap

Wrap your database calls in stored procedures

because

you will

• centralise the points of contact between your application and the database
• help to decouple your application from the database structure
• be more efficient in terms of query plans and optimisation

where code meets database

It doesn’t matter what kind of application you’re writing, if it uses a SQL Server database then at some point it will have to access that database. If you happen not to be using Entity Framework then the chances are that the lowest level of your code will be feeding SQL into some kind of data interaction object. That SQL will in turn be sent to the database, and your data will be created/read/updated/deleted. That’s going to happen, or why are you even using the database?

In this case I have only one request, and it’s this; please only ever make such a call through a stored procedure.

That is to say, at the point at which your application builds a query using SQL, make sure that what you’re actually doing is passing parameters to a stored procedure, executing it and then unpacking the results.

This is an easy tip to digest, because (I like to think) its usefulness is self-evident. It’s especially obvious if you look at it the other way round. Pretend you’re a coder who wants the most flexible interface possible. You want to be able to build a string of SQL in your app in order to send anything at all to the database. So your database access object accepts whatever query you send it. Or maybe you even build your SQL strings on the fly, dynamically, and then submit them. That’s great, you can do whatever you want!

Oh wait, but what happens if the database structure changes? If some bright DBA denormalises a table or two, turning your inner join into a piece of invalid SQL? In that case you’ll have to change your code to reflect that. Then you’ll have to make a new release of your application (at the same time as the new database structure is released, ooh, tricky). And by the way, that’s if you happen to find out about this in time! There’s no dependency checker that will work out which database changes will have knock-on effects on SQL stored within an application, so your only solutions are good communication with the DBAs and eternal vigilance.

centralisation

If however your data access call is just a wrapper for a stored procedure call, then everything changes. For a start, if you want to keep track of the points of contact between your app and the database, all you have to do is list out the names of the stored procedures used in the app. This may not sound like much of an advantage, but it is much neater than keeping track of a list of queries on views and tables.

decoupling

Secondly, our database dependencies now start and end with the stored procedures we’ve used. That is to say, it’s relatively easy for anybody changing the database structure to work out which stored procedures are affected by those changes.

This means that the task of keeping your app in synch with the database is equal to the task of monitoring a specific set of stored procedures. Later tips in this blog will show you how to improve on this even further.

Also it’s now much easier to make sure that database changes don’t break your application. It boils down to making sure that the signatures of your stored procedures (in terms of parameters in, parameters out, return values and result sets) are invariant. In other words, look after those procedures and you’re immune to database structural changes.

efficiency

The third advantage of working through stored procedures is speed. A string of dynamically-built SQL won’t, unless you’re accessing it repeatedly, have a query plan. A stored procedure is compiled, so it typically will. Accessing the database only through stored procedures will make sure that every call you make has a query plan, which typically means faster execution.

what to do now

So this is a tip that will not only improve (neater, less fragile) the way you manage SQL within your application, but also potentially improve the speed of your queries.

What can you do in order to get the benefit of this tip? Well, what you need to do right now is to go through your application code and refactor all of the SQL calls into stored procedure calls. Sorry, this may be some work. But you’ll thank me in the end!

If you need more info on how to write a stored procedure then here it is.