SQL "IF", "BEGIN", "END", "END IF"?

Not a SQL person at all. Have the following code a consultant wrote.

First, it makes sure only an elementary school has been chosen - then, after the BEGIN, if the variable @Term equals a 3 we want to do the stuff under that IF statement. Here's the problem. When @Term is not = 3 we still want to drop down and do the SECOND INSERT INTO @Classes part. FYI - the Term is = 3 when this is being run, but it's not doing both INSERT's - should there be an END IF at the end of that "IF @Term = 3" section instead of just a plain END?

IF @SchoolCategoryCode = 'Elem' 

--- We now have determined we are processing an elementary school...

BEGIN

---- Only do the following if the variable @Term equals a 3 - if it does not, skip just this first part

    IF @Term = 3

    BEGIN

        INSERT INTO @Classes

        SELECT      
        XXXXXX  
        FROM XXXX blah blah blah

    END   <----(Should this be ENDIF?)

---- **always** "fall thru" to here, no matter what @Term is equal to - always do the following INSERT for all elementary schools

    INSERT INTO @Classes    
    SELECT
    XXXXXXXX    
    FROM XXXXXX (more code) 

END

Solution 1:

It has to do with the Normal Form for the SQL language. IF statements can, by definition, only take a single SQL statement. However, there is a special kind of SQL statement which can contain multiple SQL statements, the BEGIN-END block.

If you omit the BEGIN-END block, your SQL will run fine, but it will only execute the first statement as part of the IF.

Basically, this:

IF @Term = 3
    INSERT INTO @Classes
    SELECT              
        XXXXXX  
    FROM XXXX blah blah blah

is equivalent to the same thing with the BEGIN-END block, because you are only executing a single statement. However, for the same reason that not including the curly-braces on an IF statement in a C-like language is a bad idea, it is always preferable to use BEGIN and END.

Solution 2:

There is no ENDIF in SQL.

The statement directly followig an IF is execute only when the if expression is true.

The BEGIN ... END construct is separate from the IF. It binds multiple statements together as a block that can be treated as if they were a single statement. Hence BEGIN ... END can be used directly after an IF and thus the whole block of code in the BEGIN .... END sequence will be either executed or skipped.

In your case I suspect the "(more code)" following FROM XXXXX is where your problem is.