locked
exiting from while loop RRS feed

  • Question

  • i have two while loops one inside the other and there is an if condition in 2nd while loop. depending on the if condition i want to exit from the 2nd while loop. how can i do that?

    my code:

       while(condition)

       begin

           while (condition)

           begin

               if (condition)

               begin

              --- exit on condition

               end

           end

       end

    Tuesday, August 23, 2011 6:38 PM

Answers

  • The short answer is look up BREAK and CONTINUE. The right answer is not to use loops in SQL at all. This is a set-oriented languge and we seldom use WHILE or IF-THEN logic
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Proposed as answer by Kent Waldrop Tuesday, August 23, 2011 7:04 PM
    • Marked as answer by Kalman Toth Sunday, August 28, 2011 6:45 PM
    Tuesday, August 23, 2011 6:58 PM
  • Give a look at the BREAK keyword in books online.  Here is the example from books online:

    B. 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.

      Copy Code
    USE AdventureWorks2008R2;
    GO
    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';
    
    • Proposed as answer by HunchbackMVP Tuesday, August 23, 2011 6:44 PM
    • Marked as answer by Kalman Toth Sunday, August 28, 2011 6:45 PM
    Tuesday, August 23, 2011 6:43 PM

All replies

  • Give a look at the BREAK keyword in books online.  Here is the example from books online:

    B. 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.

      Copy Code
    USE AdventureWorks2008R2;
    GO
    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';
    
    • Proposed as answer by HunchbackMVP Tuesday, August 23, 2011 6:44 PM
    • Marked as answer by Kalman Toth Sunday, August 28, 2011 6:45 PM
    Tuesday, August 23, 2011 6:43 PM
  • The short answer is look up BREAK and CONTINUE. The right answer is not to use loops in SQL at all. This is a set-oriented languge and we seldom use WHILE or IF-THEN logic
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Proposed as answer by Kent Waldrop Tuesday, August 23, 2011 7:04 PM
    • Marked as answer by Kalman Toth Sunday, August 28, 2011 6:45 PM
    Tuesday, August 23, 2011 6:58 PM
  • ... The right answer is not to use loops in SQL at all. This is a set-oriented languge and we seldom use WHILE or IF-THEN logic
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Agreed; please look into what Joe is talking about.
    Tuesday, August 23, 2011 7:03 PM