SQL Server Code Reuse Via Stored Procedures - Good or Bad Practice?

Question

I'm currently considering design options for a reporting application based on an existing dataset.

There are several clear opportunities for code reuse given that many of the reports need to use the same base data set (Edited).

The tempation is to create some base stored procedure that I can reuse throughout this system, however, the contract I was on 6 months ago or so showed me what the down side of this practice - multiple layers of - large stored procedure calls returning subsets of data in, making it very very difficult to workout what was going on, debug and test.

I'm these days of the opinion that code reuse does not necessarily enhance maintainability in database design.

I'm looking for some insight on this from a more experienced SQL Server dev than myself?

Thanks in advance.

Answer 1

Disclaimer: This isn't a "don't use stored procedures - think of the children!" post, and I'm not aiming to ignite a flame-war. I'm just suggesting code re-use is easier and possibly more suited to certain situations and platforms than others.

Code Reuse as a concept does generally improve a codebase. You keep things DRY and begin to form a layer of common functionality to solve common problems in the same way.

However like anything, one can get it wrong (with power comes responsibility blah blah blah).

It's relatively simple in most modern programming languages to reuse code, either by writing functions, or even creating whole frameworks that can be used again and again. However, in T-SQL it's tricksy because you have less options. Stored procedures can do it, but you've seen how awkward they can be.

My personal preference is to keep business logic out of the database. That means I don't use views, UDFs, sprocs etc (unless after performance profiling we think we can speed something up using these techniques) and instead keep it in my application code. This often causes thoughts of a "business logic layer" but there are various flavours of that so it's probably a misnomer. It's certainly not code directly behind UI button click handlers and so on, though.

I aim to limit the database to storing and retrieving data, because that's what they're reaaally good at. We all know how clunky and outdated T-SQL can be as a language (think exception-handling, deployment, cursors etc). Being database-agnostic is completely impossible if your application is written into the database itself, and you also can't scale your application, because the database is the application. If you have that business logic in application code, it can be scaled out much more easily.

In this case the "business logic" is the queries and transformations used to generate your reports, and I would investigate how it might be possible to reuse code in your reporting tool/code, before considering other options.