If I use your variable example I get:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I'm assuming there is a way to make it work, but then it seems like it'd be kind of a messy imperative-style approach. Would the value of @foo persist between each row's evaluation, so now my query would be capable of (intentionally or not) stateful execution? Would that interfere with the optimizer's ability to re-order evaluation or would it be undefined behavior?
Other than the special cased string value conversion, SQL server treats bit as just a very short integer type. You cannot supply a bit value alone to WHERE or CASE, you must use a comparison operator. And likewise, you cannot assign the result of a comparison expression to a bit, you must wrap it in CASE as you have demonstrated. In fact my own project, in an attempt to squeeze various dialects of SQL into a common type system, fakes booleans in T-SQL by inserting CASE expressions and ( 0) comparisons as needed See example:
http://rzsql.net/#80273F1AF9F7DB7776102BFA0814D4A7F01F2397
If a normal programming language had special places where boolean expressions could appear (like in if and while loops), and boolean expressions did not produce values in and of themselves, it would stand out as being extremely hacky.
Of course that is a complaint only for T-SQL, other dialects do have true bools. But it is emblematic of design undertaken without effort to unify fundamentals (The question not asked by T-SQL's designers: "What if predicate expressions were just like any other expression, and had a value?") and that is what I find distasteful about SQL in general.
HAVING is another example of that lazy, slap-more-syntax-on design. As you correctly point out, HAVING evaluates after the GROUP BY, WHERE before, and that's why HAVING can refer to aggregate results. But if "WHERE" was just an operator it could be used in the order it was needed without hassle. In LINQ, you write:
users
.Where(u => u.AccountIsActivated)
.GroupBy(u => u.Name)
.Select(g => new { Name = g.Key, NumberOfUsersWithName = g.Count() })
.Where(g => g.NumberOfUsersWithName > 1)
.Select(g => $"The name {g.Name} is shared by {g.NumberOfUsersWithName} users")
The .Where before the grouping is the same function as the .Where after it. No need for a special case.
Of course you could do the same thing in SQL without HAVING using a subquery:
select 'The name ' + sq.Name + ' is shared by ' + sq.NumberOfUsersWithName + ' users'
from
(select u.Name, count(*) as NumberOfUsersWithName
from Users u
where u.AccountIsActivated = 1
group by u.Name) sq
where sq.NumberOfUsersWithName > 1
But it's ugly, so they threw in HAVING and that lets you do it all in one query.
select 'The name ' + u.Name + ' is shared by ' + count(*) + ' users'
from Users u
where u.AccountIsActivated = 1
group by u.Name
having count(*) > 1
Understandable choice, because subqueries start to get unreadable fast. I believe this is due to the middle-out (select outer-stuff from inner-queries where more-outer-stuff) syntax, which gets hard to follow when nested more than a level or two. I find the front-to-back pipeline approach of LINQ or F#'s Seq module far easier to track as data gets filtered and transformed in the same sequence that the code reads.
Let's go back to my needlessly cruel and exaggerated example of "SQL math", in which all expressions are written as a fill-in-the-clauses COMPUTE statement with each operation being its own optional clause, in a fixed order. Suppose that it was decided that it's helpful to be able to MULTIPLY after adding, which normally comes _before_ multiplying in the fixed order, but people were sick of writing:
COMPUTE MULTIPLY 3 FROM (COMPUTE MULTIPLY 2 ADD 1 FROM 3)
So they came up with a new keyword for multiplying that goes _after_ the addition step.
They'd use a different word so you could tell the operators apart, but it'd basically be a synonym.
COMPUTE MULTIPLY 2 ADD 1 PRODUCT WITH 3
That's how HAVING feels to me. It's a band-aid that wouldn't be necessary or even desired if WHERE was a standalone operator on table types, just as our hypothetical PRODUCT WITH clause wouldn't be desired if we had a standalone * operator.
I get that SQL works and is extremely useful. Remember, I spent quite a bit of time implementing a library for working with it. But there are languages that make you think things like, "Oh, that's simpler than I thought. I can use this kind of construct to iterate over anything that implements the right interface. That was smart of the language designers". Then there are languages that make you think, "Huh. Another special case. Couldn't they have thought this through a bit more carefully before speccing out the language?".
SQL feels like the latter and yet I see people call it a beautiful language or their favorite language and I get a strong "stop liking what I don't like" impulse.