T-SQL Stored Procedures – who needs them?

The answer to this question may seem obvious – for a secure, well-performing application, of course we all need them!

But what about all those great legacy applications that are littered with embedded SQL? What if we are asked to bring these into the brave new world of rich interfaces and mobile apps (plus those still glued to your traditional PC)? I have seen plenty of applications with MSAccess and MySQL back-ends. When scaling these up, do we really have to create a plethora of stored procedures when all that embedded SQL blinks winningly up at us to be used as-is? This question can be equally posed for a new application, where developers with SQL experience but lacking the know-how to use stored procedures, gamely rush into creating the SQL while coding. And what about dynamic SQL? How do we deal with that?

OK, so let’s rewind… we know that stored procedures bring enormous benefits:

  1. Increased performance – the SQL is pre-compiled, unlike embedded or dynamic SQL
  2. More secure – execute access given to the stored procedures only – direct access to tables and views are denied.
  3. Better separation – the database access is contained within one layer, rather than scattered throughout the application UI code.
  4. Easier maintenance – wholesale change is easier to apply to a number of stored procedures rather than searching through reams of application code.
  5. Easier performance tuning – with the separation described above, it is easier to examine the performance of a stored procedure in isolation from UI code and fine-tune it as necessary.
  6. Easier testing – stored procedures expose an additional area of testing thereby catching bugs in a more timely manner.

And the drawbacks?

The main drawback is the increased development time and the increased skill required to build the queries (but is this a bad thing for your important application?).

As for dynamic SQL – there certainly are situations where we may want this, e.g. building a complex filter from the UI, which don’t fit the pattern of simple parameters for stored procedures.  But we can still ensure the database access is encapsulated within the stored procedure by parsing strings or using data (e.g. in table variables) and I would argue this is best practice.

So it looks like we should always aim to build our data access layer using stored procedures. But what about those drawbacks?

OK, it’s time to get real – stored procedures are not complicated – In fact they are just the same as your embedded SQL but embedded in a server-side procedure instead. That means there’s more code to write. In fact quite a lot more code as we can add auditing, error handling and security checking, amongst other very useful things. And we need a version for each CRUD action.

But, after having written many stored procedures it becomes apparent that there is an enormous amount of repetition. In fact stored procedures settle into a very definite and predictable pattern which is pretty irritating if you have to code them individually from scratch, so much so that you’d think you were missing a trick. And you would be, because let’s face it, you can generate the procedures directly from the underlying data model (i.e. the table definitions and the relationships).

In fact there is often code to generate dynamic SQL from the underlying schema within applications. The leap is to change this dynamic code such that it generates the stored procedures as part of the overall development process.

Future reading:

The stored procedure CRUD pattern laid bare (Coming Soon!)

Other (more) entertaining musings on generating stored procedures


One response to “T-SQL Stored Procedures – who needs them?

  1. I found this post by accident, and it was a year old already, so I’m just going to leave a few counterpoint comments for future readers to enjoy a more balanced experience.

    Where to begin? “we know that stored procedures bring enormous benefits”. That’s the “argument from popularity”. We don’t, actually. But I’m going to answer that with a similar “argument from popularity”: anyone having half an hour to spend with Google Search will be able to attest that the push to move as much as possible, from 1-line CRUD operations to the business logic itself, exist in just a single community: SQL Server users following Microsoft guidelines. You won’t find this issue discussed in any other community.

    Now, back to technical stuff.

    “Increased performance”. This is not a sure bet, and I’d advice anyone to *measure* before doing any kind of optimization.

    “More secure”. Apart from the fact that a) most applications hard-code the user credentials in Web.config and b) that user frequently has *all* privileges to the database, anyway, I’m not sure against what malice or which perpetrators the “security” mentioned is targeted. Is it 3rd parties, the developers themselves, bugs in the code?

    “Better separation”, “Easier maintenance”. I’d think that maintaining a separate data-access layer in C# (or any other language from GW Basic onwards) offers the same benefit and takes advantage of myriads of language features that ease that task, that are simply absent in Transact SQL.

    “Easier performance tuning”. Please. You don’t need “UI code” to be able to run data-access calls in isolation. You don’t even need dummy “Main” methods, if you use a Unit Testing framework or an interpreted environment such as IronPython or F# interactive.

    “Easier testing”. Having a separate and unit-testable data-access layer in C# is the same, only the debugger and the debugging experience is a lot better (but I’d be willing to hear people’s experience with integrated debugging from within Visual Studio).

    This kind of SP-pushing article was the norm (in the SQL Server world) around five years ago, but it is not so common nowadays, where ORMs are deeply rooted in the ecosystem and even Microsoft offers one, and LINQ is a household name (sort-of).

    The main reason I replied is because I wanted novices to know that the advice it offers is not the One True Way just because it’s more recent that the bulk of same-minded articles. Read, learn, try, test and judge for yourselves.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s