SQL WHILE Loops

Question

I have been working on creating a nested while loop in SQL, but having issues with the while loop. I think the main issue is with my outer loop. Any suggestions?

USE HW_DB;
IF OBJECT_ID('dbo.PythagoreanTriangles') IS NOT NULL
    DROP TABLE dbo.PythagoreanTriangles;
GO

CREATE TABLE PythagoreanTriangles
(
    Side1 INT NOT NULL,
    Side2 INT NOT NULL,
    Hypotenuse FLOAT NOT NULL
);

DECLARE @side1 AS INT = 1;
DECLARE @side2 AS INT = 1;
DECLARE @count AS INT = 1;
DECLARE @element  AS INT = 0;

WHILE (@side1 = @count) 
    BEGIN
        WHILE @side2 <= 10 BEGIN
            INSERT INTO dbo.PythagoreanTriangles
                VALUES (@side1, @side2, SQRT((@side2 * @side2) + (@side1 * @side1)));
            UPDATE dbo.PythagoreanTriangles SET Hypotenuse = ROUND(Hypotenuse, 2)

        SET @side2 = @side2 + 1;
        SET @count = @count + 1;
        SET @element = @element + 1;

    IF @element = 10
    BEGIN 
    SET @side1 = @side1 + 1;
    SET @element = 0;

END;
END;
END;

Output should look like the following:

1 1 1.41
1 2 2.24
1 3 ...
1 4 ...
1 5
1 6
1 7
1 8
1 9
1 10
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
..........
........ etc

9 9
9 10
10 10

what exactly are you trying to do by comparing @side1 and @count? The second time your outer 'while' is hit, @count=11 and @side1=2. — aehiilrs, Feb 26 '14 at 17:23
Just thinking further, if the problem is what I think it is, you need to assign @count=@side1 after @side1 is incremented, and change your outer loop to "while (@side1 <= 10)" — aehiilrs, Feb 26 '14 at 17:26
Nested loops seems like an unnecessary complication in this case. Why not a single loop from 1 to 10? Better yet, read @GarethD's answer. — Dan Bracuk, Feb 26 '14 at 17:35

GarethD · Accepted Answer · 2014-02-26T17:40:31.537

You can do this with a single insert, and where possible I would always advise avoiding loops/cursors.

WITH Numbers AS
(   SELECT  TOP 10 Number = ROW_NUMBER() OVER(ORDER BY object_id)
    FROM    sys.all_objects
)
INSERT dbo.PythagoreanTriangles (Side1, Side2, Hypotenuse)
SELECT  Side1 = a.Number, 
        Side2 = b.Number,
        Hypotenuse = ROUND(SQRT(POWER(a.Number, 2) + POWER(b.Number, 2)), 2)
FROM    Numbers a
        CROSS JOIN Numbers b;

Example on SQL Fiddle

Although to actually answer your question, your loop exits after one iteration because you have this condition:

WHILE (@side1 = @count)

After the first iteration of @side2 1 - 10, you do SET @side1 = @side1 + 1;. Since @Count = 1, and @Side1 is now 2, your WHILE predicate is no longer true, so the outer loop exits. Although I don't advocate this approach, to make your loop work, you would need to change your outer while predicate. Perhaps something like:

DECLARE @side1 AS INT = 1;
DECLARE @side2 AS INT = 1;

WHILE (@side1 <= 10) 
    BEGIN
        WHILE @side2 <= 10 
            BEGIN
                INSERT INTO dbo.PythagoreanTriangles
                    VALUES (@side1, @side2, SQRT((@side2 * @side2) + (@side1 * @side1)));
                UPDATE dbo.PythagoreanTriangles SET Hypotenuse = ROUND(Hypotenuse, 2)

                SET @side2 = @side2 + 1;
            END;

            SET @side2 = 1;
            SET @side1 = @side1 + 1;
    END;

Example on SQL Fiddle

Great solution, although it doesn't really help the OP with his `WHILE` loop issue. I prefer your method better than a loop though by far! — dub stylee, Feb 26 '14 at 17:30

SQL WHILE Loops

1 Answers1

Linked