Logic within Queries of SQL-Server 2012

Many times in your SQL procedures you need to test the value of a variable, then take some action based on that value.
You can do this with SQL Server’s Transact-SQL control-of-flow logic. Control-of-flow logic is a specific set of Transact-SQL commands that you can enter into your Transact-SQL procedures to dynamically control which statements are executed.
The following is a list of the keywords available in Transact-SQL:

1. BEGIN…END – Defines a block of Transact-SQL code
2. GOTO – Instructs the processor to go to a specific label defined in the procedure
3. IF…ELSE – Tests for conditions that you specify. The results of these conditions always return values of TRUE or FALSE. As such, they are known as Boolean expressions.
4. RETURN – Exits the procedure
5. WAITFOR – Delays the execution of a statement until specific conditions exist
6. WHILE – Loops until a specific condition exists
7. BREAK – Unconditionally exits a WHILE loop
8. CONTINUE – Restarts the WHILE loop
9. CASE…WHEN – Tests multiple possibilities for a given condition

The Transact-SQL statement in the following series of images uses most of the keywords listed above:

The given Transact-SQL code outline above would not execute successfully in SQL Server 2022 as-is due to the following reasons:

1. Incorrect Use of SELECT for String Literals
   • Statements like SELECT '@LoopCount is 5' and SELECT '@LoopCount is not 5' are attempting to use single quotes (') to denote string literals. While this syntax itself is valid, the intention might be to display the string value rather than execute a SELECT query that returns a string literal.
   • If the goal is to print these strings, use the PRINT statement instead:
     

     PRINT '@LoopCount is 5'
     

2. Missing Five_label
   • The label Five_label is referenced in the GOTO statement but is not defined in this snippet. Although your earlier image contains Five_label and its definition, this specific code block does not include it.
   • Without the definition of Five_label, SQL Server will raise an error:
     

     Incorrect syntax near 'Five_label'.
     

3. RETURN Statement
   • The RETURN statement is valid in Transact-SQL. However, it must be followed by an optional integer value or simply terminate the execution of the batch. In this case, RETURN is unnecessary for this logic unless it is part of a stored procedure or a function.

Here is the updated and executable version of your Transact-SQL code:

DECLARE @LoopCount int;
SET @LoopCount = 0;

WHILE @LoopCount < 10
BEGIN
    SET @LoopCount = @LoopCount + 1;
    IF @LoopCount = 5
        BREAK;
END

IF @LoopCount = 5
BEGIN
    PRINT '@LoopCount is 5';
    GOTO Five_label;
END
ELSE
    PRINT '@LoopCount is not 5';

RETURN;

Five_label:
    PRINT 'This is the Five_label';

Changes Made:
Execution in SQL Server 2022
The revised code will execute successfully and produce the following output:

@LoopCount is 5
This is the Five_label

If the `Five_label` is missing or incorrectly formatted, the code will still fail in SQL Server 2022. Ensure the entire block, including labels, is present.

This SQL Server tutorial explains how to use the WHILE LOOP in SQL Server (Transact-SQL) with syntax and examples. In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

WHILE condition
BEGIN
{...statements...}
END;

• Parameters or Arguments
  1. condition: The condition is test each pass through the loop. If condition evaluates to TRUE, the loop body is executed. If condition evaluates to FALSE, the loop is terminated.
  2. statements: The statements of code to execute each pass through the loop.
  Pay attention to the following facts:
  1. You would use a WHILE LOOP statement when you are unsure of how many times you want the loop body to execute.
  2. Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
  3. See also the BREAK statement to exit from the WHILE LOOP early.
  4. See also the CONTINUE statement to restart the WHILE LOOP from the beginning.

Note that the SELECT statement is covered in a later lesson in this module. The main point of this lesson is to show the structure of how to use logic in your queries. The actual SELECT statements shown are not significant. In the next lesson, the benefits of constructing SQL statements dynamically will be discussed.

Before moving on to the next lesson, click the Exercise link below to practice writing query logic.
Logic within Queries - Exercise