Dirty Deeds Done in SQL

Yes, I wrote a loop to find a gap in an identity-value sequence. Sue me. (Or don’t, that’d be cool too.)

dirty deeds done dirt cheap album cover ac-dc

I’ve done some things I’m not proud of. We all do, in IT, typically when we’re under-the-gun for a deadline or when the systems and frameworks in which we work have some sort of nuance or limitation that we just cannot get around, past, or over. And so we hack. We write code we’re not happy with. We even write code that we despise with every fiber of our well-intentioned being. But it has to be done. Because there’s no other choice.

With that somewhat depressing introduction, I give you my latest “dirty deed, done dirt cheap” (or for a reasonable salary, but still very reprehensible for anybody who values clean code and clean architecture). Yes, this is an actual stored-procedure signature that I wrote. Thank gods it’s not integrated into any software; it just happened to serve a niche purpose at the time. But still! I felt ICKY.

cursors are bad mkay
Did you recognize the album art from the cover pic? Just wondering… 😉
/*
Description: Search for an available ID value in Category starting 
at the low end (i.e. find a gap), because it's aesthetically pleasing 
to have Root level Categories use low ID values. Yes, really.
But mostly b/c they need to be the same on DEV & PROD environments.
*/
CREATE OR ALTER PROCEDURE dbo.[GetNextAvailableRootCategoryID]
  @StartAtID int = 10  /*
      '1' is not used but we don't feel like starting THAT low
      (that'd be like "starting over" or "starting from scratch")
      You never get a checkbook with the first check# being '1', right?
      Wait, what's a checkbook?
      Ask your parents. Or your grandparents. */
  , @MaxID int = 1000  /*
      Throw an error if we can't find any gaps before this point */
AS
BEGIN
--and you can imagine how that went...
--something about a loop, a couple IF/ELSE's, a RAISERROR or two,
--and a RETURN @val in a B-tree(eeee).
END;

I know, I know. You’re saying “wow that’s really not that bad!”. Or “wow, you REALLY couldn’t do something better about the constraints to make it less terrible?” Again, time, priorities, and energy. What’s 10 minutes in the big scheme of things, even if the code is silly and perpetuates the bad decisions that came before it? Should the overlaying systems actually depend on matching ID values? NO!!! Obviously not. But was that even on the software development roadmap for the next quarter? Also no.

So, what’ve you got? Show me your dirty deeds done in SQL. It’s okay, nobody’s judging! Except yourself, if you’ve any skin in the game. We’re all our own worst critics. But if we can’t laugh at ourselves sometimes, what the hell are we doing here? =)

Author: natethedba

I'm a SQL Server DBA, family man, and all-around computer geek.

One thought on “Dirty Deeds Done in SQL”

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