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

Answer 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 :)

Answer 2

Missing a begin and end on your while.

WHILE (Transact-SQL)