Find 40 Problems in This Stored Procedure.


Aaron Bertrand posted a challenge:

We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:

  • last placed an order more than a year ago
  • placed 3 or more orders in the past year
  • have ordered from a specific category in the past two weeks

These criteria don’t have to make sense! They just need to make the query a little bit more complex than your average CRUD operations.

First, we’re going to update Sales.SalesOrderHeader to modern times so that dates make sense relative to today. We only care about OrderDate here, but there are check constraints that protect a couple of other columns (as well as a trigger that sometimes fails on db<>fiddle but that I have no energy to troubleshoot):

This stored procedure that someone wrote will now return data (without the update, it would be hard to write predictable queries based on, say, some offset from GETDATE()).

Now, it’s your turn: how many bad practices can you find in that code?

Aaron’s answers are over here. I’ve turned off comments on this blog post because if you have any questions or thoughts, you should post ’em on Aaron’s blog post. The only reason I’m posting this here is that I bet a lot of y’all aren’t subscribed to Simple Talk, and I wanted to break this challenge up into two parts – the code to review, and the answers. Do not look at the answers until you’re done with your code review. Have fun!