asked on

How to create loop inside another loop in T-SQL?

I need to create Loop inside another loop. However I know only While @@FETCH_STATUS = 0 the way to control the loop execution. Is there any other way to apply some other method to another loop? Please advise! Thank you.

Kevin Cross

Hi.

You can have other conditions other than that of CURSOR @@FETCH_STATUS; therefore, it may be just a syntax error if you are trying this and getting incorrect results, or an error.

Here is an example:

DECLARE @i INT = 0, @j INT = 0;

WHILE @i < 12
BEGIN
    SET @i = @i + 1;

    WHILE @j < 28
    BEGIN
        SET @j = @j + 1;
        PRINT DATEFROMPARTS(2014, @i, @j);
    END

    SET @j = 0;
END

Open in new window

Technet reference: http://technet.microsoft.com/en-us/library/ms178642.aspx

I hope that helps!

Best regards and happy coding,

Kevin

ASKER CERTIFIED SOLUTION

5teveo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Regarding my example above, I just realize that I used DATEFROMPARTS, which may not work for you as that is a newer function.

I hope you got the point of how the syntax works, though.

DECLARE @i INT = 0, @j INT = 0;

WHILE @i < 12
BEGIN
    SET @i = @i + 1;

    WHILE @j < 28
    BEGIN
        SET @j = @j + 1;
        PRINT CONVERT(VARCHAR(10), @i)+'-'+CONVERT(VARCHAR(10), @j)
    END

    SET @j = 0;
END

Open in new window

If you are looking for something specific to cursors, you can check the other Expert's advice.