T-SQL How to Finish an IF-ELSE IF-ELSE Block

Question

When I run the procedure below with the correct parameters, so that -1 is not returned, none of my DML statements are triggered. I assume that it processes all my DML instructions as part of a block ELSE.

SQL Server 2014

How to 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

Answer 1

It’s good that you should use Beginit Endin the instructions Else, since it contains several 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

Answer 2

, SELECT -1, RETURN ELSE, BEGIN/END. SELECT -1 else.

,

ELSE
  BEGIN
    SELECT -1;
    RETURN;
  END

Answer 3

.

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.

1- ELSE ELSE. RETURN . BEGIN TRY .

:

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

Answer 4

ELSE , ELSE IF, 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

Answer 5

RETURN.

BEGIN END SQL, -, , . #, , . , , , .

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

IF(1=2) SELECT 1
   SELECT 2

, , SELECT 1 .

:

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

Answer 6

( ) CASE WHEN, @Code. MSDN , CASE :

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

I find the code makes it more readable for simple evaluations (but this may be a personal preference).

Your code will look similar to this (pseudo code not verified):

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 

Read more about CASEhere:

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