Solved

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

Posted on 2014-01-10
3
319 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now