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
This question is related to
sql
Off hand the code looks right. What if you try using an 'Else' and see what happens?
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
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END <----(Should this be ENDIF?)
ELSE
BEGIN
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END
END
The only time the second insert into @clases should fail to fire is if an error occurred in the first insert statement.
If that's the case, then you need to decide if the second statement should run prior to the first OR if you need a transaction in order to perform a rollback.
You could also rewrite the code to remove the nested 'If' statement completely.
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX
Where @Term = 3
---- **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)
Blockquote
Just look at it like brackets in .NET
IF
BEGIN
do something
END
ELSE
BEGIN
do something
ELSE
equal to
if
{
do something
}
else
{
do something
}
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.
If I remember correctly, and more often then not I do ... there is no END IF support in Transact-Sql. The BEGIN and END should do the job. Are you getting errors?
If this is MS Sql Server then what you have should work fine... In fact, technically, you don;t need the Begin & End at all, snce there's only one statement in the begin-End Block... (I assume @Classes is a table variable ?)
If @Term = 3
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX blah blah blah
-- -----------------------------
-- This next should always run, if the first code did not throw an exception...
INSERT INTO @Classes
SELECT XXXXXXXX
FROM XXXXXX (more code)
You could also rewrite the code to remove the nested 'If' statement completely.
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX
Where @Term = 3
---- **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)
Based on your description of what you want to do, the code seems to be correct as it is. ENDIF isn't a valid SQL loop control keyword. Are you sure that the INSERTS are actually pulling data to put into @Classes? In fact, if it was bad it just wouldn't run.
What you might want to try is to put a few PRINT statements in there. Put a PRINT above each of the INSERTS just outputting some silly text to show that that line is executing. If you get both outputs, then your SELECT...INSERT... is suspect. You could also just do the SELECT in place of the PRINT (that is, without the INSERT) and see exactly what data is being pulled.
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.
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.
Based on your description of what you want to do, the code seems to be correct as it is. ENDIF isn't a valid SQL loop control keyword. Are you sure that the INSERTS are actually pulling data to put into @Classes? In fact, if it was bad it just wouldn't run.
What you might want to try is to put a few PRINT statements in there. Put a PRINT above each of the INSERTS just outputting some silly text to show that that line is executing. If you get both outputs, then your SELECT...INSERT... is suspect. You could also just do the SELECT in place of the PRINT (that is, without the INSERT) and see exactly what data is being pulled.
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.
If this is MS Sql Server then what you have should work fine... In fact, technically, you don;t need the Begin & End at all, snce there's only one statement in the begin-End Block... (I assume @Classes is a table variable ?)
If @Term = 3
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX blah blah blah
-- -----------------------------
-- This next should always run, if the first code did not throw an exception...
INSERT INTO @Classes
SELECT XXXXXXXX
FROM XXXXXX (more code)
The only time the second insert into @clases should fail to fire is if an error occurred in the first insert statement.
If that's the case, then you need to decide if the second statement should run prior to the first OR if you need a transaction in order to perform a rollback.
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.
The only time the second insert into @clases should fail to fire is if an error occurred in the first insert statement.
If that's the case, then you need to decide if the second statement should run prior to the first OR if you need a transaction in order to perform a rollback.
Off hand the code looks right. What if you try using an 'Else' and see what happens?
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
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END <----(Should this be ENDIF?)
ELSE
BEGIN
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END
END
Based on your description of what you want to do, the code seems to be correct as it is. ENDIF isn't a valid SQL loop control keyword. Are you sure that the INSERTS are actually pulling data to put into @Classes? In fact, if it was bad it just wouldn't run.
What you might want to try is to put a few PRINT statements in there. Put a PRINT above each of the INSERTS just outputting some silly text to show that that line is executing. If you get both outputs, then your SELECT...INSERT... is suspect. You could also just do the SELECT in place of the PRINT (that is, without the INSERT) and see exactly what data is being pulled.
You could also rewrite the code to remove the nested 'If' statement completely.
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX
Where @Term = 3
---- **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)
If this is MS Sql Server then what you have should work fine... In fact, technically, you don;t need the Begin & End at all, snce there's only one statement in the begin-End Block... (I assume @Classes is a table variable ?)
If @Term = 3
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX blah blah blah
-- -----------------------------
-- This next should always run, if the first code did not throw an exception...
INSERT INTO @Classes
SELECT XXXXXXXX
FROM XXXXXX (more code)
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.
Based on your description of what you want to do, the code seems to be correct as it is. ENDIF isn't a valid SQL loop control keyword. Are you sure that the INSERTS are actually pulling data to put into @Classes? In fact, if it was bad it just wouldn't run.
What you might want to try is to put a few PRINT statements in there. Put a PRINT above each of the INSERTS just outputting some silly text to show that that line is executing. If you get both outputs, then your SELECT...INSERT... is suspect. You could also just do the SELECT in place of the PRINT (that is, without the INSERT) and see exactly what data is being pulled.
If I remember correctly, and more often then not I do ... there is no END IF support in Transact-Sql. The BEGIN and END should do the job. Are you getting errors?
You could also rewrite the code to remove the nested 'If' statement completely.
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX
Where @Term = 3
---- **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)
If I remember correctly, and more often then not I do ... there is no END IF support in Transact-Sql. The BEGIN and END should do the job. Are you getting errors?
The only time the second insert into @clases should fail to fire is if an error occurred in the first insert statement.
If that's the case, then you need to decide if the second statement should run prior to the first OR if you need a transaction in order to perform a rollback.
Off hand the code looks right. What if you try using an 'Else' and see what happens?
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
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END <----(Should this be ENDIF?)
ELSE
BEGIN
INSERT INTO @Classes
SELECT
XXXXXXXX
FROM XXXXXX (more code)
END
END
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.
Blockquote
Just look at it like brackets in .NET
IF
BEGIN
do something
END
ELSE
BEGIN
do something
ELSE
equal to
if
{
do something
}
else
{
do something
}
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.
If this is MS Sql Server then what you have should work fine... In fact, technically, you don;t need the Begin & End at all, snce there's only one statement in the begin-End Block... (I assume @Classes is a table variable ?)
If @Term = 3
INSERT INTO @Classes
SELECT XXXXXX
FROM XXXX blah blah blah
-- -----------------------------
-- This next should always run, if the first code did not throw an exception...
INSERT INTO @Classes
SELECT XXXXXXXX
FROM XXXXXX (more code)
Source: Stackoverflow.com