title: While loop with if statements in SQL not working
body: <p>I am new in using loop in SQL Server. I just wanted to ask why my code is not working? I am trying to have if statements within a loop and it will continue to process until it gets what it wanted.  Thanks for any response!</p>
  
  <pre><code>DECLARE @SubjectCategoryID bigint
  DECLARE @ParentID bigint
  DECLARE @EntityID bigint
  DECLARE @isLocation int
  DECLARE @tempTable TABLE (ParentID bigint, isLocation int) 
  DECLARE @projectCodesTable TABLE (Contingency nvarchar(max), Provincial nvarchar(max), HQAdmin nvarchar(max))    
  DECLARE @count int
  
  Select @SubjectCategoryID = SubjectCategoryID, @EntityID = EntityID  from t_Project WHERE Code = '1000296'
  SET @count = 0
  SET @isLocation = 0
  
  WHILE (@isLocation = 1)
  BEGIN
      DELETE FROM @tempTable
      IF @SubjectCategoryID = 150     -- Village
      BEGIN
          INSERT INTO @tempTable
              SELECT CommunityID, IsLocation from t_Loc_Village WHERE VillageID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 140
      END
      ELSE IF @SubjectCategoryID = 140    --- Community
      BEGIN
          INSERT INTO @tempTable
              SELECT CityTownID, IsLocation from t_Loc_Community WHERE CommunityID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 135
      END
      ELSE IF @SubjectCategoryID = 135    --- City/Town
      BEGIN
          INSERT INTO @tempTable
              SELECT ProvinceID, IsLocation from t_Loc_CityTown WHERE CityTownID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 130
      END
      ELSE IF @SubjectCategoryID = 130    --- Province
      BEGIN
          INSERT INTO @tempTable
              SELECT RegionalOfficeID, IsLocation from t_Loc_Province WHERE ProvinceID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 210
      END
      ELSE IF @SubjectCategoryID = 210    --- Regional Office
      BEGIN
          INSERT INTO @tempTable
              SELECT CountryID, IsLocation from t_RegionalOffice WHERE RegionalOfficeID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 100
      END
      ELSE IF @SubjectCategoryID = 100    --- Country
      BEGIN
          INSERT INTO @tempTable
              SELECT 0, IsLocation from t_Loc_Country WHERE CountryID = @EntityID
  
          Select @EntityID = ParentID, @isLocation = isLocation From @tempTable
          SET @SubjectCategoryID = 0
      END
  END
  
  Select * from @tempTable
  </code></pre>