Bad Stored Procedures

Voice of the DBA

I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

Or is it?

I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.