Overall, even for a non-first-timer with SQL, this is a good query. The consistency and styling makes it very clear you're not a programming newbie.
To answer your concern about "under" versus "under or on", and 0 meaning an empty wiki - I totally agree. For me, 0 meaning empty is the only logical interpretation - empty strings have length 0 in every language I've come across. The way you've named your parameters (MaxSomething) implies clearly that the user is inputting the highest number that will be included in the results - and that's what they get. This is all perfectly clear and sensible to me, no need to worry.
You don't need the
END wrappers here. They don't hurt, but they're only required for control-of-flow blocks (usually,
You don't need
@LEN_MAX in the current version of the query.
For a "report" type query like this, I'd always prefer to have default ordering set by the query (an
ORDER BY clause) so the results are guaranteed consistent.
The WHERE clause
Looking at this half a year on from your original post, I can see you've refined the
WHERE clause yourself. At the time of my writing the
WHERE clause on the linked SEDE query is:
(@max_body_length >= 0 AND LEN(pWiki.body) <= @max_body_length)
OR (@max_excerpt_length >= 0 AND LEN(pExcerpt.body) <= @max_excerpt_length)
This is definitely a much clearer way to express your desired logic than the
CASE statement in the original post. For me, the reason it's clearer is that it maps quite easily to reading in plain English:
Show me rows where the max body length is 0 or more and the length of the Wiki Body is less than or equal to that, or where...
I reckon we could go further though :)
Different operators can improve readability
This is a matter of opinion only, but if your aim is to make query logic easily readable in plain English, sometimes inverting your operators can help that.
@max_body_length >= 0
"max body length is greater than or equal to 0, I mean 0 or more"
This is what I think when I read this code; I re-word it post-hoc to "0 or more" after understanding the meaning.
@max_body_length !< 0
"max_body_length is not less than 0"
For me this reads more clearly first-pass, and doesn't need a post-hoc re-scan. It'll depend a lot on the reader and how familiar they are with
! as a negation operator (some people prefer
An even shorter alternative
Shorter isn't always better: for complex logic, spelling it out step by step (more verbosely) as you have can be much clearer, because of the "mapping to plain English" property.
However, we could go shorter here using
nullif() to equate your
-1 default values with SQL
NULL i.e. "unknown"/"not applicable":
LEN(pWiki.body) <= nullif(@max_body_length,-1)
or LEN(pExcerpt.body) <= nullif(@max_excerpt_length,-1)
This takes advantage of the all-consuming nature of
NULL: if I ask "is the length of this (known) thing less than an unknown number", the only possible answer can be "I don't know" - another
Then, because SQL
NULL doesn't evaluate to
true, the overall
WHERE clause as a whole works out just as you intend.
However, the catch here (you probably spotted it) is that this only works for
-1 as a default, not any negative input. Since negative inputs apart from -1 are meaningless in this query, you could validate this at the start of the query:
IF (@max_body_length<-1) or (@max_excerpt_length<-1)
RAISERROR('Input parameters should be -1, 0 or positive integers',16,1)
Frankly this is probably overkill for a SEDE query but demonstrates the right approach for more generic application.
Ideally, one would actually make the default input for each parameter be
NULL (rather than -1) and only permit 0 or positive input values: then you wouldn't need the
nullif functions at all. However I'm not aware of a way to do that in SEDE.