{"id":37147,"date":"2024-07-29T11:47:42","date_gmt":"2024-07-29T01:47:42","guid":{"rendered":"https:\/\/database.guide\/?p=37147"},"modified":"2024-07-29T11:47:44","modified_gmt":"2024-07-29T01:47:44","slug":"sql-server-while-loop-with-examples","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-server-while-loop-with-examples\/","title":{"rendered":"SQL Server WHILE Loop (with Examples)"},"content":{"rendered":"\n

The WHILE<\/code> loop in SQL Server is a control-flow statement that allows us to repeatedly execute a block of code as long as a specified condition is true. It’s useful for iterative tasks and processing data in batches.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The basic syntax goes like this:<\/p>\n\n\n\n

WHILE condition\nBEGIN\n    -- Code to be executed\nEND<\/code><\/pre>\n\n\n\n
Key points:<\/p>\n\n\n\n
    \n
  1. The condition is evaluated before each iteration.<\/li>\n\n\n\n
  2. If the condition is true, the code block executes.<\/li>\n\n\n\n
  3. If the condition is false, the loop terminates.<\/li>\n<\/ol>\n\n\n\n
    The SQL Server documentation provides the following, more specific, syntax (which is basically an elaboration on the above simple syntax):<\/p>\n\n\n\n
    WHILE boolean_expression\n    { sql_statement | statement_block | BREAK | CONTINUE }<\/code><\/pre>\n\n\n\n
    The following examples demonstrate the how the syntax translates to actual T-SQL<\/a> code.<\/p>\n\n\n\n
    Example<\/h2>\n\n\n\n
    Here’s an example of a simple WHILE<\/code> loop:<\/p>\n\n\n\n
    DECLARE @counter INT = 1;\n\nWHILE @counter <= 5\nBEGIN\n    PRINT 'Iteration: ' + CAST(@counter AS VARCHAR(2));\n    SET @counter = @counter + 1;\nEND<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    Iteration: 1
    Iteration: 2
    Iteration: 3
    Iteration: 4
    Iteration: 5<\/pre>\n\n\n\n
    WHILE<\/code> Loop with BREAK<\/code><\/h2>\n\n\n\n
    The BREAK<\/code> argument causes an exit from the innermost WHILE<\/code> loop. Any statements that appear after the END<\/code> keyword, marking the end of the loop, are executed.<\/p>\n\n\n\n
    Example:<\/p>\n\n\n\n
    DECLARE @num INT = 1;\n\nWHILE 1 = 1 -- Infinite loop\nBEGIN\n    IF @num > 5\n        BREAK;\n    PRINT @num;\n    SET @num = @num + 1;\nEND<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    1
    2
    3
    4
    5<\/pre>\n\n\n\n
    While this example uses an infinite loop, we usually want to avoid infinite loops. But the example demonstrates that we can break out of the loop based on a certain condition being met.<\/p>\n\n\n\n
    WHILE<\/code> Loop with CONTINUE<\/code><\/h2>\n\n\n\n
    The CONTINUE<\/code> argument restarts a WHILE<\/code> loop. Any statements after the CONTINUE<\/code> keyword are ignored. CONTINUE<\/code> is often opened by an IF<\/code> test.<\/p>\n\n\n\n
    Example:<\/p>\n\n\n\n
    DECLARE @i INT = 0;\n\nWHILE @i < 10\nBEGIN\n    SET @i = @i + 1;\n    IF @i % 2 = 0\n        CONTINUE;\n    PRINT 'Odd number: ' + CAST(@i AS VARCHAR(2));\nEND<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    Odd number: 1
    Odd number: 3
    Odd number: 5
    Odd number: 7
    Odd number: 9<\/pre>\n\n\n\n
    Nested WHILE<\/code> Loop<\/h2>\n\n\n\n
    It’s possible to nest WHILE<\/code> loops, so that we have a loop within another loop. <\/p>\n\n\n\n
    Here’s a simple example:<\/p>\n\n\n\n
    DECLARE @outer_counter INT = 1;\nDECLARE @inner_counter INT;\n\nWHILE @outer_counter <= 3\nBEGIN\n    PRINT 'Outer loop iteration: ' + CAST(@outer_counter AS VARCHAR(2));\n    \n    SET @inner_counter = 1;\n    \n    WHILE @inner_counter <= 5\n    BEGIN\n        PRINT '  Inner loop iteration: ' + CAST(@inner_counter AS VARCHAR(2));\n        \n        SET @inner_counter = @inner_counter + 1;\n    END\n    \n    PRINT 'Outer loop continues after inner loop completes';\n    SET @outer_counter = @outer_counter + 1;\nEND\n\nPRINT 'All loops completed';<\/code><\/pre>\n\n\n\n
    Output:<\/p>\n\n\n\n
    Outer loop iteration: 1
      Inner loop iteration: 1
      Inner loop iteration: 2
      Inner loop iteration: 3
      Inner loop iteration: 4
      Inner loop iteration: 5
    Outer loop continues after inner loop completes
    Outer loop iteration: 2
      Inner loop iteration: 1
      Inner loop iteration: 2
      Inner loop iteration: 3
      Inner loop iteration: 4
      Inner loop iteration: 5
    Outer loop continues after inner loop completes
    Outer loop iteration: 3
      Inner loop iteration: 1
      Inner loop iteration: 2
      Inner loop iteration: 3
      Inner loop iteration: 4
      Inner loop iteration: 5
    Outer loop continues after inner loop completes
    All loops completed<\/pre>\n\n\n\n
    If the inner loop contains a BREAK<\/code>, it exits to the next outermost loop. All the statements after the end of the inner loop run first, and then the next outermost loop restarts:<\/p>\n\n\n\n
    DECLARE @outer_counter INT = 1;\nDECLARE @inner_counter INT;\n\nWHILE @outer_counter <= 3\nBEGIN\n    PRINT 'Outer loop iteration: ' + CAST(@outer_counter AS VARCHAR(2));\n    \n    SET @inner_counter = 1;\n    \n    WHILE @inner_counter <= 5\n    BEGIN\n        PRINT '  Inner loop iteration: ' + CAST(@inner_counter AS VARCHAR(2));\n        \n        IF @inner_counter = 2\n        BEGIN\n            PRINT '  Breaking inner loop';\n            BREAK;\n        END\n        \n        SET @inner_counter = @inner_counter + 1;\n    END\n    \n    PRINT 'Outer loop continues after inner loop break';\n    SET @outer_counter = @outer_counter + 1;\nEND\n\nPRINT 'All loops completed';<\/code><\/pre>\n\n\n\n
    Output:<\/p>\n\n\n\n
    Outer loop iteration: 1
      Inner loop iteration: 1
      Inner loop iteration: 2
      Breaking inner loop
    Outer loop continues after inner loop break
    Outer loop iteration: 2
      Inner loop iteration: 1
      Inner loop iteration: 2
      Breaking inner loop
    Outer loop continues after inner loop break
    Outer loop iteration: 3
      Inner loop iteration: 1
      Inner loop iteration: 2
      Breaking inner loop
    Outer loop continues after inner loop break
    All loops completed<\/pre>\n\n\n\n
    WHILE<\/code> Loop for Data Processing<\/h2>\n\n\n\n
    Suppose we create the following table:<\/p>\n\n\n\n
    -- Create the Orders table\nCREATE TABLE Orders (\n    OrderID INT IDENTITY(1,1) PRIMARY KEY,\n    CustomerName VARCHAR(100),\n    OrderDate DATE,\n    TotalAmount DECIMAL(10,2),\n    Processed BIT DEFAULT 0\n);\n\n-- Insert sample data\nINSERT INTO Orders (CustomerName, OrderDate, TotalAmount)\nVALUES \n    ('John Doe', '2024-07-20', 150.00),\n    ('Jane Smith', '2024-07-21', 200.50),\n    ('Bob Johnson', '2024-07-22', 75.25),\n    ('Alice Brown', '2024-07-23', 300.00),\n    ('Charlie Davis', '2024-07-24', 125.75);\n\n-- View the initial data\nSELECT * FROM Orders;<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    OrderID  CustomerName   OrderDate                 TotalAmount  Processed
    -------  -------------  ------------------------  -----------  ---------
    1        John Doe       2024-07-20T00:00:00.000Z  150          false    
    2        Jane Smith     2024-07-21T00:00:00.000Z  200.5        false    
    3        Bob Johnson    2024-07-22T00:00:00.000Z  75.25        false    
    4        Alice Brown    2024-07-23T00:00:00.000Z  300          false    
    5        Charlie Davis  2024-07-24T00:00:00.000Z  125.75       false    <\/pre>\n\n\n\n
    We can use the following code to “process” the orders, one by one with a WHILE<\/code> loop:<\/p>\n\n\n\n
    -- Process orders using a WHILE loop\nDECLARE @id INT;\n\nWHILE EXISTS (SELECT 1 FROM Orders WHERE Processed = 0)\nBEGIN\n    SELECT TOP 1 @id = OrderID FROM Orders WHERE Processed = 0;\n    \n    -- Process the order (in this example, we're just marking it as processed)\n    UPDATE Orders \n    SET Processed = 1 \n    WHERE OrderID = @id;\n    \n    -- Simulate some processing time\n    WAITFOR DELAY '00:00:01';\n    \n    PRINT 'Processed OrderID: ' + CAST(@id AS VARCHAR(10));\nEND<\/code><\/pre>\n\n\n\n
    Output:<\/p>\n\n\n\n
    (1 row affected)
    Processed OrderID: 1
    (1 row affected)
    Processed OrderID: 2
    (1 row affected)
    Processed OrderID: 3
    (1 row affected)
    Processed OrderID: 4
    (1 row affected)
    Processed OrderID: 5<\/pre>\n\n\n\n
    In the real world we would do some actual processing, but seeing as this is just a quick example, all we do is set the Processed<\/code> column to 1<\/code> (for true<\/code>) and simulate a bit of processing time.<\/p>\n\n\n\n
    Once that code has run, we can check the data in the table again:<\/p>\n\n\n\n
    SELECT * FROM Orders;<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    OrderID  CustomerName   OrderDate                 TotalAmount  Processed
    -------  -------------  ------------------------  -----------  ---------
    1        John Doe       2024-07-20T00:00:00.000Z  150          true     
    2        Jane Smith     2024-07-21T00:00:00.000Z  200.5        true     
    3        Bob Johnson    2024-07-22T00:00:00.000Z  75.25        true     
    4        Alice Brown    2024-07-23T00:00:00.000Z  300          true     
    5        Charlie Davis  2024-07-24T00:00:00.000Z  125.75       true     <\/pre>\n\n\n\n
    We can see that all orders have now been processed.<\/p>\n\n\n\n
    These examples demonstrate that the WHILE<\/code> loop can be a great tool in SQL Server for handling repetitive tasks. However, it’s a good idea to use it judiciously, as set-based operations are often more efficient for large-scale data processing.<\/p>\n","protected":false},"excerpt":{"rendered":"
    The WHILE loop in SQL Server is a control-flow statement that allows us to repeatedly execute a block of code as long as a specified condition is true. It’s useful for iterative tasks and processing data in batches.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[77,61,20],"class_list":["post-37147","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-mssql","tag-t-sql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=37147"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37147\/revisions"}],"predecessor-version":[{"id":37398,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37147\/revisions\/37398"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=37147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=37147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=37147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}