The BEGIN…END keywords identify a code block. We typically use them to group Transact-SQL statements. The BEGIN…END blocks can be nested. We also use BEGIN…END statements to identify a code block in an IF…ELSE clause, WHILE loop, and CASE element. The following is the basic syntax for the BEGIN…END keyword block:

BEGIN
     {
    sql_statement | statement_block
     }
END

The following is a basic example of BEGIN…END:

USE [AdventureWorks2012];
GO

BEGIN
    DECLARE @Today [datetime];
    SET @Today = CURRENT_TIMESTAMP;

    SELECT TOP 100
            *
    FROM    [HumanResources].[vEmployee];

    SELECT  @Today;
END

The IF…ELSE block is simply used to make processing decisions based on Boolean (true/false) expressions. For example, it tells SQL Server to run a Transact-SQL statement or a set of statements if the Boolean expression specified in the IF clause returns True, or optionally run an alternate Transact-SQL statement or set of statements if the Boolean expression specified in the IF clause returns False.

We can have an IF clause without an ELSE clause; however, an ELSE clause cannot exist without an IF clause. The IF…ELSE statements can be nested, meaning that an IF or ELSE clause can contain another IF…ELSE structure. The basic syntax for the IF…ELSE block follows:

IF Boolean_expression { sql_statement | statement_block }
[ ELSE { sql_statement | statement_block } ]

When the block of statements is used in an IF…ELSE block, then you must use the BEGIN…END keywords to identify whether the block is in the IF clause or the ELSE clause. Moreover, if you include a SELECT statement in the expression, you must enclose the statement in parentheses. Here is a basic example of an IF…ELSE block:

IF ( SELECT DATENAME(dw, CURRENT_TIMESTAMP)
   ) IN ( N'Friday', N'Saturday', N'Sunday' )
    BEGIN
        SELECT  'Hey, its weekend!!!';
    END;
ELSE
    BEGIN
        SELECT  N'Its weekday!!!';
    END;

We can also use the CASE statement to make decisions based on an expression. The CASE statement is a conceptually simpler way to perform operations similar to IF…ELSE IF…ELSE. The basic syntax for a CASE statement is as follows:

CASE input_expression
WHEN test_result THEN statement_block
WHEN test_result THEN statement_block
…
[ELSE statement_block]
END

The input_expression parameter is the value that is tested by the WHEN statements. If the input_expression parameter includes a SELECT statement, then you must enclose the SELECT statement in parentheses. The following is a basic example of the CASE statement:

SELECT CASE (SELECT DATENAME(dw, CURRENT_TIMESTAMP))
        WHEN N'Friday' THEN N'Hey, Its Friday!!!'
        WHEN N'Saturday' THEN N'Hey, its Saturday!!!'
        WHEN N'Sunday' THEN N'Hey, its Sunday!!!'
        ELSE N'Its weekday!!!'
       END;

The WHILE statement is a basic looping construct in SQL Server that is based on a Boolean expression. As long as the expression evaluates to true, SQL Server continues to repeat the execution of the specified T-SQL statement or code block. The basic syntax of the WHILE loop is as follows:

WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }

The optional keywords BREAK and CONTINUE can be included with the WHILE loop and are used to control the logic inside the loop. When you specify the BREAK keyword with the WHILE loop, it exits the innermost WHILE loop (in nested loops). If in the outer loop, SQL Server exits the WHILE loop and continues with the next statement. On the other hand, when you specify the CONTINUE keyword, SQL Server restarts the loop at the first statement in the block and ignores any statements following the CONTINUE keyword. Here is a basic example of the WHILE loop:

DECLARE @counter [int] = 0
WHILE ( @counter < 10 )
    BEGIN
        IF ( @counter < 5 )
            BEGIN
                SELECT  @counter;
                SET @counter = @counter + 1;
                CONTINUE;
            END;
        ELSE
          SET @counter = @counter + 1;
            IF @counter = 7
                BEGIN
                    SELECT  @counter;
                    BREAK;
                END;
    END;

We use the RETURN keyword to unconditionally end the procedure, batch, or statement block. We use the GOTO keyword to transfer the execution context of the statement from its current point to the specified line in the GOTO label. SQL Server ignores any statements between these. We use the WAITFOR keyword to suspend execution until the specified time of day is reached, or an interval (up to 24 hours) has passed. The time can be supplied as a literal or with a variable.