T-SQL How to end an IF-ELSE IF-ELSE block

问题:

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.

SQL Server 2014

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

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

回答1:

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

    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:

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.

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

回答3:

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.

So you need

ELSE
  BEGIN
    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 @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 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:

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 

More on the CASE statement here:

https://msdn.microsoft.com/en-us/library/ms181765.aspx

回答6:

In your example, RETURN always runs.

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.

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.

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