SQL IF-ELSE and WHILE examples

As like the conditional processing constructs, SQL flow control statements are a critical part of your understanding of SQL. You may be asked a direct question; most likely it will be a part of a larger question.

Transact-SQL provides special words called control-of-flow language that control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. These control-of-flow words are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

One interesting question that I have seen where even seasoned candidates’ trip is when asked to explain transaction rollbacks.

BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
    SELECT FirstName, MiddleName 
    FROM Person.Person WHERE LastName = 'Adams';
    ROLLBACK TRANSACTION;
    PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO
/*
Rolled back the tranaction.
*/

In the above example, BEGIN and END define a series of Transact-SQL statements that execute together. If the BEGIN...END block were not included, both ROLLBACK TRANSACTION statements would execute and both PRINT messages would be returned.

Using IF…ELSE

IF...ELSE evaluates a Boolean expression, and if TRUE, executes a Transact-SQL statement or batch. The syntax is simple:

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

Boolean_expression  -  Is an expression that returns TRUE or FALSE. If the Boolean_expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement | statement_block } - Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Using a simple Boolean expression

The following example has a simple Boolean expression (1=1) that is true and, therefore, prints the first statement.

IF 1 = 1 PRINT 'Boolean_expression is true.'
ELSE PRINT 'Boolean_expression is false.' ;

Using a query as part of a Boolean expression

The following example executes a query as part of the Boolean expression.

IF 
    (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
        PRINT 'There are more than 5 Touring-3000 bicycles.'
    ELSE 
        PRINT 'There are 5 or less Touring-3000 bicycles.' ;

Using nested IF...ELSE statements

The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.

DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
   PRINT 'The number is large.';
ELSE 
   BEGIN
      IF @Number < 10
      PRINT 'The number is small.';
   ELSE
      PRINT 'The number is medium.';
   END ;
GO

Using WHILE statement

WHILE allows you to set a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Using BREAK and CONTINUE with nested IF...ELSE and WHILE

In the following example, if the average list price of a product is less than $300, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE loop and prints a message.

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
   SELECT MAX(ListPrice) FROM Production.Product
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear';