Nested while loop in stored procedures

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

, +

I Just Updating the Missing Records in the Exisiting table.

I try the Nested While Loop whereas the inner loop works well
but the Outer loop doesn't works out for me. I checked lot of times, for me it seems correctly. Where I did Mistake please Sort it out friends .

Thanks in Advance

[edit]Manually indented code - OriginalGriff[/edit]

What I have tried:

SQL

Alter Procedure spExemptedLeaveSettings
As
Begin
    Declare @iLeaveID int = 1 
    Declare @iSMID int = 1133
    Declare @cSex varchar
    Set @cSex = (Select cSex from StaffMaster where iSMID = @iSMID) 
    While(@iSMID <= 1662)
    Begin  
        While(@iLeaveID <= 15)
        Begin 
            if(@cSex = 'M')
            begin
                Insert into StaffLeaveSettings Values (@iSMID,@iLeaveID,0,'E',1,0,'I',Null,Null,Null,Null,Null,Null,Null,Null)
                Update StaffLeaveSettings Set iRefreshYear = 5 where iLeaveID = 4 and iSMID = @iSMID
                Select cSex from StaffMaster where iSMID = @iSMID
                Delete StaffLeaveSettings Where iLeaveID IN (2,5,7,10,11) and iSMID = @iSMID
            end
            else if(@cSex = 'F') 
            begin	
                Insert into StaffLeaveSettings Values (@iSMID,@iLeaveID,0,'E',1,0,'I',Null,Null,Null,Null,Null,Null,Null,Null)
                Update StaffLeaveSettings Set iRefreshYear = 5 where iLeaveID = 4 and iSMID = @iSMID
                Select cSex from StaffMaster where iSMID = @iSMID
                Delete StaffLeaveSettings Where iLeaveID IN (2,7,8,10,11) and iSMID = @iSMID
            end
            Set @iLeaveID = @iLeaveID + 1
        End
        Set @iSMID = @iSMID + 1
    End
End

Posted 12-Jul-16 23:06pm

Mohammed Asarudeen R

Updated 12-Jul-16 23:59pm

OriginalGriff

v3

Add a Solution

Comments

Suvendu Shekhar Giri 13-Jul-16 5:47am

How you knew that outer loop is not working?

Mohammed Asarudeen R 13-Jul-16 5:50am

Yes!

abinash panda 13-Jul-16 5:57am

It looks like your both the loops are working. but after the first pass of outer loop when it comes for the second iteration , @iLeaveID has already reached the max limit so its not entering the inner loop anymore hence it looks like your outer loop is not working more than once but it is looping each time failing to enter the inner loop. Hope it helps. My suggestion you should reinitialize the value of @iLeaveID some where before the inner loop and in the outer loop. i.e. set @iLeaveID = 1;
after begin.

While(@iSMID <= 1662)
Begin
While(@iLeaveID <= 15)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abinash panda · Accepted Answer · 2016-07-12T23:59:00

Solution 1

  Declare @cSex varchar
    Set @cSex = (Select cSex from StaffMaster where iSMID = @iSMID) 
    While(@iSMID <= 1662)
    Begin  
set @iLeaveID = 1;
        While(@iLeaveID <= 15)

SQL

Try the above code.

Posted 12-Jul-16 23:59pm

abinash panda

Comments

Mohammed Asarudeen R 13-Jul-16 6:04am

Thanks Dude

abinash panda 13-Jul-16 6:31am

:)