SQL WHILE Loops

loops, sql, while-loop

Solution

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

Problem

Original source