Proper use of statement terminator

Question

Proper use of statement terminator

dbaker-592842

Old Hand

Points: 352
More actions
March 5, 2012 at 3:20 pm

#251893

Several have suggested that we start using the statement terminator, as it will be required in a "later" release of SQL Server.
There is little (if any) MS documentation on proper use of the statement terminator.
But some common sense about what is and is not a statement should get you there.
In this example (from MS Books T-SQL for the IF statement), it's implied that a statement block is not considered a statement, but the entire IF...ELSE construct is a statement.
Note that the statements within the IF statement block are terminated, but the IF block itself (first half of the IF/ELSE) is not terminated.
The entire IF...ELSE statement is terminated with a semi-colon after the END for the ELSE block.
USE AdventureWorks2008R2;
GO
DECLARE @AvgWeight decimal(8,2), @BikeCount int
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
SET @BikeCount =
(SELECT COUNT(*)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
END
ELSE
BEGIN
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%' );
PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GO
Now the terminator is "optional" (in most cases), for now.
I would interpret that to mean that you can leave it off, but if you use it, it should be used properly.
So I would expect code like the following to fail in parsing, because the IF statement is terminated, and then a new statement beginning with ELSE starts.
IF 1 = 1
BEGIN
print '1 = 1';
print 'All is well.';
END;
ELSE
print 'The world is upside down.';
But it does not fail.
Am I missing something (in what WILL be required), or is the current parser just way to lenient to be of use to those who are starting to add statement terminators to all their code?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I'm with Celko on this one. Put them where it seems to make sense to you and then see if it parses first.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I ahve to agree with Joe and Jason here. There are some inconsistencies in where you can and should put the terminator. In fact you BEGIN and END are both statements that can and I think should be terminated. So in your last example you'd really want this:

IF 1 = 1

BEGIN;

print '1 = 1';

print 'All is well.';

END;

ELSE

print 'The world is upside down.';

All I did was add a terminator AFTER the BEGIN. But in the case of a Try Catch block in T-SQL you can't put a terminator after END TRY or BEGIN CATCH.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dbaker-592842 Old Hand Points: 352 More actions · Answer 3

Makes sense to do what makes sense, whether or not the parser is completely committed at this time.

MS does not consistently use the terminator in all examples, but where they do, they are consistent.

Their patterns indicate that BEGIN and END are not statements, but statement block markers -- so you'll see a terminator after an END of the second block in an IF/ELSE, or after the IF block in the case of IF without ELSE. The terminator doesn't terminate the END itself, but the IF/ELSE, or IF statement.

MS logic on TRY/CATCH makes sense. TRY and CATCH have to go together as a unit. A terminator on the END of the TRY block, or the BEGIN of the CATCH block would end the statement before its required 2nd half. (This would be similar to the WITH/statement pattern for common table expressions. You terminate after both required halves, not after the WITH clause.)

So I'm doing what would make sense in a structured programming language. We may not have had everything thouroughly checked by a parser, but we'll be close.

Thanks for the input. dbaker