Proper use of statement terminator

  • 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?

  • 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

  • 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.

  • 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

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

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