How does one use loops in TSQL?

Question

In TSQL, I would like to change the following code from have to use hard coded dhomes to using a loop for optimization. My failed attempt at trying to add a loop is also included.

Declare  @dhome Tinyint, @bp smallint, @lr smallint, @q smallint

    // Set @dhome = 1
  While(@dhome <= 3) // My attempt to add a loop

  SELECT @lr = MAX(NQdDate), @q = NQd
  FROM NQdHistory
  WHERE dhomeId = @dhome 
  GROUP BY NQdDate, NQd

  SELECT @bd = COUNT(*)
  FROM bdhome
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  DELETE FROM ND1 WITH(XLOCK)
  WHERE dhomeID= @dhome  AND NQdDate= @lr

  UPDATE NQdHistory
  SET Nbd = @q - @@RowCount - @bp, NBd = @bp
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  Set @dhome = @dhome +1 //My attempt to end a loop

Solution 1

You're on the right track. You're missing your begin and end. Also, be sure to give @dhome a value. It looks like you started to and have it commented out on your third line:

Declare  @dhome Tinyint, @bp smallint, @lr smallint, @q smallint

    // Set @dhome = 1
While(@dhome <= 3) // My attempt to add a loop
begin
  SELECT @lr = MAX(NQdDate), @q = NQd
  FROM NQdHistory
  WHERE dhomeId = @dhome 
  GROUP BY NQdDate, NQd

  SELECT @bd = COUNT(*)
  FROM bdhome
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  DELETE FROM ND1 WITH(XLOCK)
  WHERE dhomeID= @dhome  AND NQdDate= @lr

  UPDATE NQdHistory
  SET Nbd = @q - @@RowCount - @bp, NBd = @bp
  WHERE NQdDate= @lr AND dhomeID= @dhome 

  Set @dhome = @dhome +1 //My attempt to end a loop
end  

If you're familiar with C/C#/C++, think of T-SQL's Begin and End like curly braces { and }, if you're more familiar with VB Then and End If. Or more like pascals Begin and End. You get the idea :)

Solution 2

Missing a begin and end on your while.

WHILE (Transact-SQL)

Solution 3

Example 1

DECLARE @I INT,@COUNTVAR INT
SET @I = 1
DECLARE @Parent_Child TABLE(ID INT IDENTITY(1,1),ParentPositionID INT NULL,ChildPositionId Int)

INSERT INTO @Parent_Child(ParentPositionID,ChildPositionId) 
SELECT DISTINCT PARENT_POSITION_ID,CHILD_POSITION_ID from tblPOSITION_HIERARCHY 
--WHERE CHILD_POSITION_ID IN (--YOUR CONDITION IF ANY)
SELECT @COUNTVAR =COUNT(*) FROM @PTS_Parent_Child
DECLARE @int_SUPE_POSITION_ID INT, @int_CHILD_POSITION_ID INT
 --loop through records here 
WHILE @I <= @COUNTVAR
BEGIN
SELECT @int_SUPE_POSITION_ID=ParentPositionID,@int_CHILD_POSITION_ID=ChildPositionId  FROM @Parent_Child WHERE ID=@I   
--Whatever you want to do with records
SET @I=@I+1
END

Example 2

Just another approach if you are fine using temp tables.I have personally tested this and it will not cause any exception (even if temp table does not have any data.)

CREATE TABLE #TempTable
    (
      ROWID int identity(1,1) primary key,
      HIERARCHY_ID_TO_UPDATE int,
    )
    --INSERT DATA INTO TEMP TABLE USING INSERT INTO CLAUSE OR FOR EAXMPLE BELOW
    --INSERT INTO #TempTable VALUES(1)
    --INSERT INTO #TempTable VALUES(2)
    --INSERT INTO #TempTable VALUES(4)
    --INSERT INTO #TempTable VALUES(6)
    --INSERT INTO ##TempTable VALUES(8)


    DECLARE @MAXID INT
    SET @COUNTER =1
    SELECT @MAXID=COUNT(*) FROM #TempTable
    --PRINT @MAXID
    WHILE (@MAXID > 0)
    BEGIN
         --DO THE PROCESSING HERE 
         SELECT @HIERARCHY_ID_TO_UPDATE =PT.HIERARCHY_ID_TO_UPDATE FROM      #TempTable  PT WHERE ROWID=@COUNTER
         --PRINT '@MAXID VALUE '
         --PRINT  @MAXID
        SET @MAXID=@MAXID-1
        SET @COUNTER =@COUNTER + 1
    End


    If(OBJECT_ID('tempdb..#TempTable') IS NOT NULL)
    BEGIN
        DROP TABLE #TempTable
    END

Comments

[user1880670]

Would it be the same way if I did a for loop in TSQL?

[Adam Plocher]

There's not really a such thing as a FOR loop in T-SQL. The code in the answer basically simulates the functionality of a FOR loop, however.

[user1880670]

Thanks for the assistance Adam!