Control-of-flow statements:

For SQL Server Stored Procedures: Part-1, Click here.

BEGIN….END Statement:

The BEGIN….END statement contains set of  SQL statement and executes block of SQL statement.

Syntax:

BEGIN
    { SQL Statement || Statement Block}
END

For example:

BEGIN
    SELECT
        First_Name,
        Salary
    FROM
        dbo.EmployeeDb
    WHERE
        Salary> 100000;

    IF @@ROWCOUNT=  0
        PRINT 'No one has greater than 100000';
END

Syntax Description:

• Block of SQL statement starts with BEGIN and Ends with END.
• Inside BEGIN….END statement SELECT is used for finding name of employee who has salary more than 100000.
• IF statement is used to print message if no one have salary more than 100000.

Nesting of BEGIN….END Statement:

BEGIN….END statement also can be nested.

For Example:

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name    
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary DESC  

    IF @@ROWCOUNT <>  0
       BEGIN
              PRINT 'Employee with Highest salary is: '+@firstName
       END
       ELSE
       BEGIN
        PRINT 'No one is selected'
       END
END

IF Statement:

Syntax of IF statement:

IF BooleanExpression  
BEGIN
    { Statement Block || SQL Statement}
END

 For Example:

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name    
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary DESC  
    IF @@ROWCOUNT > 0
       BEGIN
           PRINT 'Employee with Highest salary is: '+@firstName
       END            
END

Output:

IF ELSE Statement:
 

IF BooleanExpession
BEGIN
   {Statement Block|| SQL Statement}
END
ELSE
BEGIN
   {Statement Block|| SQL Statement}
END

Syntax of IF ELSE Statement:

For example:
 

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name     
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary   
    IF @@ROWCOUNT <>  0
       BEGIN
              PRINT 'Employee with Lowest salary is: '+@firstName
       END
       ELSE
       BEGIN
        PRINT 'No one is selected'
       END
END

Output:

Nested IF ELSE Statement:

IF ELSE statement can also be nested.

For example:
 

BEGIN
    DECLARE @A INT = 100,
            @B INT = 500;
    IF (@A > 0)
    BEGIN
        IF (@A < @B)
            PRINT 'A > 0 and A < B';
        ELSE
            PRINT 'A > 0 and A >= B';
    END
END

Output:

WHILE Statement:

Syntax:

WHILE BooleanExpression
      {SQL Statement ||Statement Block}

It takes one expression and check its true or false and executes SQL statement or Statement block.

For Example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    PRINT @count;
    SET @count = @count + 1;
END

Syntax description:

@count is used as counter of integer type and its value is set to 0.

Then, WHILE statement is use to check the condition. If the condition is true it print @count and @count value is increased by 1. It stops printing if condition is false.

Output:

BREAK Statement:

BREAK statement is used to exit current loop or iteration.

Syntax:

WHILE BooleanExpression1
BEGIN
   --statement
   IF BooleanExpression2
       BREAK
   --statement
END

For example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    SET @count = @count + 1;
    IF @count = 4
        BREAK;
    PRINT @count;
END

Syntax description:

DECLARE is used for declaring @count, WHILE is used for checking condition and SET is used to increase counting.

IF statement checks the condition and if condition is true then BREAK statement is executed which will end the loop.

Output:

CONTINUE Statement:

If CONTINUE statement is encountered current iteration is skipped and loop continues to execute iterations until given condition is false.

Syntax:

WHILE BooleanExpression1
BEGIN
   --statement to be executed
   IF BooleanExpression2
      CONTINUE
   --statement is skipped if condition matches
END

For example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    SET @count = @count + 1;
    IF @count = 4
        CONTINUE;
    PRINT @count;
END

Syntax description:

DECLARE is used for declaring @count, WHILE is used for checking condition and SET is used to increase counting.

IF statement checks the condition and if condition is true then CONTINUE statement is executed which skip the next line and keeps on executing statements.

Output:

For SQL Server Stored Procedure: Part-3, Click here.