Solved

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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