T-SQL如何结束IF-ELSE IF-ELSE块

When I run the below procedure with the correct parameters so that the -1 value isn't returned, none of my DML statements are firing. I'm guessing that it's treating all my DML statements as part of the ELSE block.

当我使用正确的参数运行以下过程以便不返回-1值时,我的DML语句都没有被触发。我猜它是把我所有的DML语句都当作ELSE块的一部分。

SQL Server 2014

SQL Server 2014

How do I end an IF-ELSE-ELSE-IF block?

如何结束IF-ELSE-ELSE-IF块?

ALTER PROCEDURE [GenerateNumber] (
    @Code VARCHAR(2)
)
AS
BEGIN
    DECLARE @stringConcat VARCHAR = 'X';

    IF @Code = 'KP'
        SET @stringConcat += 'Y';
    ELSE IF @Code = 'RL'
        SET @stringConcat += 'Z';
    ElSE
        -- Return error code and stop processing
        SELECT -1;
        RETURN;

    BEGIN TRY
        -- Various DML statements...

        SELECT @successValue;
        RETURN;
    END TRY
    BEGIN CATCH
        SELECT -1;
        RETURN;
    END CATCH
END

6 个解决方案

#1

Okay you have to use Begin and End in the Else statement as it contains multiple lines of code.

好的,你必须在Else语句中使用Begin和End,因为它包含多行代码。

IF @Code = 'KP'
        SET @stringConcat += 'Y';
    ELSE IF @Code = 'RL'
        SET @stringConcat += 'Z';
    ElSE
    Begin
        -- Return error code and stop processing
        SELECT -1;
        RETURN;
    End

#2

If you want both SELECT -1 and RETURN to be inside the ELSE you'll have to use a BEGIN / END block. Now only the SELECT -1 is inside the else branch.

如果你想让SELECT -1和RETURN都在ELSE中,你将不得不使用BEGIN / END块。现在只有SELECT -1在else分支内。

So you need

所以你需要

ELSE
  BEGIN
    SELECT -1;
    RETURN;
  END

#3

Your indenting is lying to you.

你的缩进对你说谎。

IF @Code = 'KP'
     SET @stringConcat += 'Y';
 ELSE IF @Code = 'RL'
     SET @stringConcat += 'Z';
 ElSE
     -- Return error code and stop processing
     SELECT -1;  -- THIS is evaluated as the ELSE
     RETURN;     -- THIS is run regardless.

Only the 1st line after that last ELSE will be executed as an ELSE condidion. That RETURN will be run regardless. Your BEGIN TRY can't be reached.

只有最后一个ELSE之后的第一行才会被执行为ELSE条件。 RETURN将无论如何都会运行。无法联系到您的BEGIN TRY。

Try this:

IF @Code = 'KP'
     SET @stringConcat += 'Y';
 ELSE IF @Code = 'RL'
     SET @stringConcat += 'Z';
 ElSE
     BEGIN
     -- Return error code and stop processing
     SELECT -1;
     RETURN;
     END

#4

The last ELSE in your If, ELSE IF, ELSE contains multiple lines of code. You need to start it with BEGIN and end it with END. See this MSDN documentation for more details.

If,ELSE IF,ELSE中的最后一个ELSE包含多行代码。您需要使用BEGIN启动它并以END结束它。有关更多详细信息,请参阅此MSDN文档。

IF @Code = 'KP'
    SET @stringConcat += 'Y';
ELSE IF @Code = 'RL'
    SET @stringConcat += 'Z';
ElSE
    BEGIN
        -- Return error code and stop processing
        SELECT -1;
        RETURN;
    END

#5

In your example, RETURN always runs.

在您的示例中,RETURN始终运行。

From a coding practice standard, you should always use BEGIN and END in SQL in my opinion to clearly state what is intended to be in the logical block. I prefer the same pattern in C# where I use braces even when not needed. The indentation is important in my opinion as well as it easily lets you track where it starts and finishes.

从编码实践标准来看,我应该始终在SQL中使用BEGIN和END来清楚地说明逻辑块中的内容。我更喜欢C#中的相同模式,即使不需要也可以使用大括号。在我看来,缩进很重要,它可以轻松地跟踪它的开始和结束位置。

IF(1=2)
   BEGIN
      SELECT 1
   END
SELECT 2

IF(1=2) SELECT 1
   SELECT 2

These are equivalent in behavior, but the first clearly shows SELECT 1 is dependent on the logical condition above it.

这些在行为上是等价的,但第一个清楚地表明SELECT 1依赖于它上面的逻辑条件。

What you really want is:

你真正想要的是:

IF @Code = 'KP'
    BEGIN
        SET @stringConcat += 'Y';
    END
ELSE IF @Code = 'RL'
    BEGIN
        SET @stringConcat += 'Z';
    END
ElSE
    BEGIN
        -- Return error code and stop processing
        SELECT -1;
        RETURN;
    END

#6

In your case (pun intended), you might be better off using a CASE WHEN construction, seeing you want to evaluate different values for your @Code variable. MSDN states that CASE is intended exactly for such scenarios:

在您的情况下(双关语),您可能最好使用CASE WHEN构造,看到您想要评估@Code变量的不同值。 MSDN声明CASE适用于以下场景:

Evaluates a list of conditions and returns one of multiple possible result expressions.

I find it makes code a bit more readable for simple evaluations (but that could very well be a personal preference).

我发现它使代码对于简单的评估更具可读性(但这很可能是个人偏好)。

Your code would end up looking similar to this (pseudo-code. not tested):

您的代码最终看起来与此类似(伪代码。未经测试):

CASE @Code 
    WHEN 'KP' THEN SET @stringConcat += 'Y';
    WHEN 'RL' THEN SET @stringConcat += 'Z';
    ElSE
        -- Return error code and stop processing
        SELECT -1;
        RETURN;
END

#1