[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2014-01-10
3
Medium Priority
?
339 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 61

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 2000 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 61

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

611 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