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.
BurzhuinAsked:
Who is Participating?
 
5teveoConnect With a Mentor Commented:
DECLARE w_cursor CURSOR FOR
 SELECT  PgmNamex, DATEDIFF(day, min(LastRun), GETDATE())
            From
                        @tTable1
            group by PgmNamex
            order by PgmNamex

OPEN w_cursor

FETCH_NEXT:

FETCH NEXT FROM w_cursor INTO @vPgmName, @vDateDiff

IF @@FETCH_STATUS <> 0
            BEGIN
                        Goto CLOSE_CURSOR
            END

<Code here>
GOTO NEXT_LOOP
NEXT_LOOP_RETURN:

            
Goto FETCH_NEXT

CLOSE_CURSOR:

CLOSE w_cursor
DEALLOCATE w_cursor






NEXT_LOOP:
DECLARE w_cursor2 CURSOR FOR
 SELECT  PgmNamex, DATEDIFF(day, min(LastRun), GETDATE())
            From
                        @tTable1
            group by PgmNamex
            order by PgmNamex

OPEN w_cursor2

FETCH_NEXT2:

FETCH NEXT FROM w_cursor2 INTO @vPgmName, @vDateDiff

IF @@FETCH_STATUS <> 0
            BEGIN
                        Goto CLOSE_CURSOR2
            END

<Code here>
            
Goto FETCH_NEXT2

CLOSE_CURSOR2:

CLOSE w_cursor2
DEALLOCATE w_cursor2




GOTO NEXT_LOOP_RETURN:
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.