How to use loops in TSQL?

Question

In TSQL, I would like to modify the following code to use hard-coded dhomes to use a loop for optimization. My unsuccessful attempt to try 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 are on the right track. You do not have enough beginning and end. Also, be sure to include the @dhome value. It looks like you started and commented 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 are familiar with C / C # / C ++, think of T-SQL Begin and End as curly braces { and } if you are more familiar with VB Then and End If . Or more like Pascals Begin and End . You get the idea :)

Answer 2

There is no beginning or end to your time.

WHILE (Transact-SQL)

Answer 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

Another approach if you make good use of temporary tables. I personally checked this and it will not throw any exceptions (even if there is no data in the temp table.)

 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