[See an index of all bad habits / best practices posts]
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:
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:
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:
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:
If you put a semi-colon after END TRY, you’ll get not one error, but two:
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.
[See an index of all bad habits / best practices posts]
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.
Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.
