在存储过程中嵌套while循环 [英] Nested while loop in stored procedures

问题描述

我正在更新现有表中的缺失记录。



我尝试嵌套While循环，而内循环效果很好

但是外环对我来说不起作用。我检查了很多次，对我来说似乎是正确的。在哪里我做了错误请把它排序给朋友。



预付谢谢



手动缩进代码 - OriginalGriff [/ edit]



我尝试过：

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:

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

推荐答案

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

尝试上面的代码。

Try the above code.

这篇关于在存储过程中嵌套while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！