A large part of most DBA/DBD’s daily job is writing & maintaining
stored-procedures. In SQL Server or other RDBMSs, stored-procs (“SP’s”, “procs”, however you like to abbreviate), serve as one of the building-blocks of your overlaying applications and day-to-day operations, including maintenance and automation.
Today, something struck me, and I was both shocked and comforted by the fact that this hadn’t really “come back to bite me in the arse“, as the proverbial saying goes. But first, some context.
When we declare our proc signature with our parameters, we of course give them
datatypes, and often
default values — the parameter value that is assumed & used upon execution when the caller (operator, application, agent job, etc.) calls said proc without passing a value to that parameter. So we create our proc like so:
CREATE PROCEDURE dbo.MyProc @MyParam BIT = 0 AS BEGIN SET NOCOUNT ON; END
So that users are allowed to call it like so, and assume some correct default behavior:
CREATE line is part of a typical “boilerplate” snippet or template which I use to create procs with “create if not exists, else alter” logic and a nice header-comment-block, which I’ll publish on my GitHub or Gist shortly, so I can show it here. I know that MS recently added
DROP IF EXISTS support to the language, but frankly, I like to keep procs intact if they exist because it’s easier not to have to remember/re-apply their metadata, such as security (grants/deny’s, certificate signatures, etc.) and extended properties.
Wake me up when they add true Oh snap, they did… in 2016 SP1. Anyway.
CREATE OR ALTER syntax!
Now for the “catch”, the gotcha.
If the caller says
exec dbo.MyProc, that’s great — they didn’t pass a parameter value, so the execution uses the default value (
0) and off we go. However, if the caller is so malicious as to literally pass
NULL, we might have a problem! Because let’s say that
@MyParam value is used in a
JOIN predicate or a
IN (SELECT...) block, or even a
CASE expression. We won’t get an actual error; SQL Server is smart enough to jump over the syntactical variations required for equivalence checking (i.e.
Column1 = 0 vs.
Column1 is NULL) when it interprets/compiles the stored-procedure. But, what we’re probably going to get is unexpected or unknown behavior.
It seemed worth re-using a classic…
And really, it all comes back to those nasty things called assumptions. See, as the proc author, we’re assuming that our
@MyParam will always be a
1, because it’s a
BIT, and we gave it a default value, right? Sure, maybe in another language, but this is
NULL is a separate and distinct thing, a valid value for any datatype, and must be accounted for and treated as such. It can get especially dicey when you have a
NOT IN (SELECT...) block that ends up as an empty-set, which suddenly morphs the outer query into a “without a
WHERE clause” beast, and.. well, you can guess the rest.
So what do we do about it? Well, we can add a “check parameter values” block to the top of our procedure where we either throw an error, or set the NULL value back to a default.
IF (@MyParam IS NULL) RAISERROR ('@MyParam cannot be NULL; try again.', 15, 1);
IF (@MyParam IS NULL) SET @MyParam = 0;
We could also work on the internal proc logic to account for
NULL values and “eliminate the guesswork” (i.e. prevent unexpected behavior) by actually having logical branches/conditions which “do something” if the parameter is
NULL. Then, at least we know what our proc will do if that infamous caller does
exec MyProc @MyParam = NULL. Yay! But that sounds like a lot of work. Maybe.
Or maybe it’s worthwhile because you actually want
NULL to be treated differently than all other parameter values, and then, hey, you’ve already spent the time on that logic, so you’re done!
I hope this helps somebody else avoid the same assumptions.