Solved

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

Posted on 2014-01-10
3
324 Views
Last Modified: 2014-01-10
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.
0
Comment
Question by:Burzhuin
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39771750
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
 
LVL 8

Accepted Solution

by:
5teveo earned 500 total points
ID: 39771754
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39771773
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question