Loops in SQL Server

1. SQL Server IF.....ELSE statement

In SQL Server, IF.....ELSE statements is used to execute code when a condition is TRUE or execute different code if condition evaluate to FALSE.

Syntax

IF condition
{….statements to execute when condition is TRUE...}
ELSE
{….statements to execute when condition is FALSE...}


Example: Illustration of IF....ELSE statement in SQL server.

DECLARE @Stud_value INT;
SET @stud_value = 8;
IF @stud_value <10
    PRINT 'Mark'
ELSE
    PRINT 'Andrew';
GO


In above example, if the variable @stud_value is less than 10 then print the value 'Mark'. Otherwise print the value 'Andrew'.

Example: Illustration of nested IF.....ELSE Statement in SQL Server.

SET @Stud_value = 8;
IF @Stud_value < 10
   PRINT 'Mark';
ELSE
BEGIN
   IF @stud_value < 30
      PRINT 'Andrew';
   ELSE
      PRINT 'Anna';
END;
GO

2. SQL Server WHILE LOOP

SQL Server WHILE LOOP is used when user is not sure about how many times to execute the loop body.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body.

Syntax

WHILE [condition]
BEGIN
{statements}
END;


Example: Illustration of how to use WHILE LOOP in SQL server.

DECLARE @stud_value INT;
SET @stud_value = 0;
WHILE @stud_value <= 10
BEGIN
PRINT 'Mark';
SET @stud_value = @stud_value + 1;
END;
PRINT 'Andrew';
GO


In this WHILE LOOP example, the loop would terminate, if the @stud_value exceeded 10 as specified by:
WHILE @stud_value <= 10

3. SQL Server BREAK Statement

The SQL Server BREAK Statement is used to exit from a WHILE LOOP and execute the next statement after the loop's statement.

Syntax:
BREAK;

Example: Illustration of BREAK Statement in SQL Server.

DECLARE @stud_value INT;
SET @stud_value = 0;
WHILE @stud_value <= 10
BEGIN
IF @stud_value = 2
BREAK;
ELSE
PRINT 'Mark';
SET @stud_value = @stud_value + 1;
END;
PRINT 'Andrew';
GO

4. SQL Server CONTINUE Statement

SQL Server CONTINUE statement is used when user wants to execute a WHILE LOOP again.

Syntax:
CONTINUE;
  

Example: Illustration of CONTINUE Statement in SQL Server.

DECLARE @stud_value INT;
SET @stud_value = 0;
WHILE @stud_value <= 10
BEGIN
IF @stud_value = 2
BREAK;
ELSE
BEGIN
SET @stud_value = @stud_value + 1;
PRINT 'Mark';
CONTINUE;
END;
PRINT 'Andrew';
GO


In above example, the WHILE LOOP will restart if the variable @stud_value is not equal to 2, as specified by the IF...ELSE statement.

5. SQL Server GOTO Statement

  • SQL Server GOTO statement alters the flow of execution to a label.
  • The statements that follow GOTO are skipped and processing continues at the label.
  • GOTO statements and labels can be used anywhere within a procedure, batch, or statement block.
Syntax:
GOTO lable_name;

Note:
  • lable_name must be unique within the scope of the code.
  • There should be at least one statement to execute after the label declaration.

Example: Illustration of GOTO statement in SQL Server.

DECLARE @stud_value INT;
SET @stud_value = 0;
WHILE @stud_value <= 10
BEGIN
IF @stud_value = 2
GOTO 'Mark;
SET @stud_value = @stud_value + 1;
END;
Mark:
PRINT ' Mark';
GO


In above example, GOTO statement is created and labeled as 'Mark'. If @stud_value is equals to 2, then the code will branch to the label 'Mark'.  
▲