SQL“IF”、“BEGIN”、“END”、“END IF”？

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

提问by foxxtrot

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?

首先，它确保只选择了一所小学 - 然后，在 BEGIN 之后，如果变量 @Term 等于 3，我们想要在该 IF 语句下进行操作。这就是问题所在。当@Term 不是 = 3 时，我们仍然希望下拉并执行 SECOND INSERT INTO @Classes 部分。仅供参考 - 运行时 Term 为 = 3，但它并没有同时执行 INSERT - 在“IF @Term = 3”部分的末尾是否应该有一个 END IF 而不是一个简单的 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

回答by foxxtrot

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

它与 SQL 语言的范式有关。IF根据定义，语句只能采用 单个SQL 语句。但是，有一种特殊的 SQL 语句可以包含多个 SQL 语句，即BEGIN-END块。

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

如果省略该BEGIN-END块，您的 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-ENDblock, because you are only executing a single statement. However, for the same reason that not including the curly-braces on an IFstatement in a C-like language is a bad idea, it is alwayspreferable to use BEGINand END.

与BEGIN-END块等效，因为您只执行单个语句。然而，对于不包括一个在大括号同样的原因IF在一份声明中类C语言是一个坏主意，它总是最好使用BEGIN和END。

回答by AnthonyWJones

There is no ENDIF in SQL.

SQL 中没有 ENDIF。

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

直接跟在 IF 之后的语句仅在 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.

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

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

在您的情况下，我怀疑 FROM XXXXX 后面的“（更多代码）”是您的问题所在。

回答by SquidScareMe

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

回答by user25623

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) 

回答by Charles Bretana

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)

回答by mattruma

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 中没有 END IF 支持。BEGIN 和 END 应该可以完成这项工作。你有错误吗？

回答by NotMe

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.

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

回答by RivkaZ

Blockquote

块引用

Just look at it like brackets in .NET

就像 .NET 中的括号一样看待它

IF
  BEGIN
      do something
  END
ELSE 
  BEGIN 
      do something
  ELSE

equal to

等于

if
{
   do something
}
else
{
   do something
}

回答by Michael Bray

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.

您可能想要尝试的是在其中放入一些 PRINT 语句。在每个 INSERTS 上面放一个 PRINT 只是输出一些愚蠢的文本来显示该行正在执行。如果你得到两个输出，那么你的 SELECT...INSERT... 是可疑的。您也可以只执行 SELECT 代替 PRINT（即，没有 INSERT）并准确查看正在提取的数据。