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:
BEGIN TRANSACTION EXEC ProcedureName COMMIT TRANSACTION
But when they removed the
EXEC, the stored procedure suddenly "worked":
BEGIN TRANSACTION ProcedureName COMMIT TRANSACTION
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:
The command(s) completed successfully.
What was actually happening in the above case was that they were simply starting a named transaction that happened to have the same name as their stored procedure, not doing anything inside that transaction, and then committing. The code was equivalent to the following, which makes it much more obvious what is going on:
BEGIN TRANSACTION ProcedureName; COMMIT TRANSACTION;
Which means their stored procedure wasn't even being called - so of course it couldn't possibly have generated any errors. This would have been caught long earlier had they written it the "right" way in the first place. Statement terminators and/or the schema prefix would have ensured that the code wasn't falsely accused of succeeding:
BEGIN TRANSACTION; EXEC dbo.ProcedureName; COMMIT TRANSACTION;
So, once again, I strongly, strongly, strongly recommend you get in the habit of accurately referencing all objects with their schema in all scenarios, and properly terminating all relevant statements with semi-colons.
As an aside, though, note that not all statements are eligible for termination; for example, I was surprised when I learned that three out of the four statements in the
TRY/CATCH construct can have semi-colons, but one of them can't:
BEGIN TRY; SELECT 1/0; END TRY -- <-- *** not valid here *** BEGIN CATCH; PRINT 'ruh roh'; END CATCH;
If you put a semi-colon after
END TRY, you'll get not one error, but two:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ';'. Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'CATCH'.
The reason, while not intuitive, is that
TRY/CATCH is one statement "block," and there is no functional reason to terminate
END TRY - since no statement is valid between
END TRY and
BEGIN CATCH. But this is an exception that shouldn't discourage you from trying.