Bad Habits: Another Case for Semi-colons and Schema Prefix
Over 5 years ago - before I even really started my "Bad habits" series - I gave several reasons why you should start using semi-colons to terminate all eligible statements. It's not really because I believe Microsoft will actually enforce the deprecation; after all, that change would literally break millions and millions of lines of code. It's much easier over time to relax rules than to tighten them. Still, even then, my thinking was: Better safe than sorry. I gave a few examples in that article, such as the fact that the statement preceding a common table expression (CTE) must be terminated with a semi-colon, and commands like MERGE
and certain Service Broker statements also require semi-colons to avoid generating syntax errors.
About a month later, I started this "Bad habits" series, and one of the first posts was about the schema prefix. I explained several reasons you should always include the schema for any object you reference in any way. The most notable reason is that if you have users with different default schemas, they can actually get different but redundant plans for the exact same query.
The other day on Stack Overflow, I came across a poor unfortunate soul who was getting simultaneously bitten by both of these bad habits. They had a stored procedure that was raising an error when called like this:
But when they removed the EXEC
, the stored procedure suddenly "worked":
They tried removing the EXEC
because they were under the impression that you could call a stored procedure without EXEC
as long as you didn't need to pass any parameters. (The reality is that you can only call a procedure without EXEC
when it is the first statement in the batch, and it has nothing to do with parameters. Of course can and should are two different things.) This was compounded because they were also under the impression that the procedure now "worked" simply because it didn't raise an error. However there was no debugging code in the stored procedure at all, so the way they evaluated that it was "working" was that the messages pane simply returned a success message: