SQL " IF "， " BEGIN "， " END "， " END IF " ?

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

根本不是SQL人。请咨询师编写以下代码。

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?

首先，它确保只选择了一所小学——然后，在开始之后，如果变量@Term等于3，我们想在if语句下进行操作。问题就在这里。当@Term不等于3时，我们仍然希望下拉并将第二个插入到@Classes部分。FYI——运行时术语是= 3，但它不是同时执行插入操作——如果在“IF @Term = 3”部分的结尾而不是简单的结尾，应该有一个结尾吗?

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

9 个解决方案

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

它与SQL语言的常规形式有关。根据定义，如果语句可以只接受一条SQL语句。但是，有一种特殊的SQL语句，它可以包含多个SQL语句，即起始端块。

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.

如果省略了开始端块，SQL将运行良好，但它将只执行第一个语句作为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're 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.

与BEGIN-END块相同，因为您只执行一个语句。然而，出于同样的原因，在类c语言中不包含if语句的花括号是个坏主意，使用BEGIN和END总是更可取的。

#2

There is no ENDIF in SQL.

SQL中没有ENDIF。

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

语句仅在IF表达式为真时才执行。

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.

开始……END构造与IF是分开的。它将多个语句绑定在一起，作为一个块，可以将它们视为单个语句。因此开始……结束后可以直接使用,如果因此整个代码块的开始....结束序列将被执行或跳过。

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

在你的情况下，我怀疑XXXXX后面的“(更多的代码)”就是你的问题所在。

#3

Off hand the code looks right. What if you try using an 'Else' and see what happens?

马上代码看起来是正确的。如果你尝试使用“Else”，看看会发生什么?

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

#4

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

如果这是MS Sql Server，那么您所拥有的应该可以正常工作……实际上，从技术上讲，你根本不需要开始和结束，因为在开始端块中只有一个语句……(我假设@Classes是一个表变量?)

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)

#5

You could also rewrite the code to remove the nested 'If' statement completely.

您还可以重写代码以完全删除嵌套的“If”语句。

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)

#6

The only time the second insert into @clases should fail to fire is if an error occurred in the first insert statement.

第二次插入@clases失败的唯一情况是第一次插入语句中出现错误。

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.

如果是这种情况，那么您需要决定第二个语句应该在第一个语句之前运行，还是需要一个事务来执行回滚。

#7

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?

如果我没记错的话，我通常会……如果在Transact-Sql中支持，则没有结束。开始和结束应该完成这项工作。你的错误吗?

#8

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.

根据您对要做的事情的描述，代码看起来是正确的。ENDIF不是一个有效的SQL循环控制关键字。您确定插入实际上正在将数据拉入@Classes吗?事实上，如果它是坏的，它就不会运行。

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.

您可能想尝试的是在其中放入一些打印语句。在每个插入的上面放一个打印输出，输出一些愚蠢的文本，以显示该行正在执行。如果您同时获得两个输出，那么您的SELECT…INSERT…是令人怀疑的。您还可以在不使用INSERT的地方执行SELECT(也就是说，不使用INSERT)，并确切地查看正在提取的数据。

#9

-9

Frankly this seems like something that should be on the application layer, not the database layer...

坦白地说，这看起来应该在应用层，而不是数据库层……

#1