We help IT Professionals succeed at work.

ms sql server  - loop thru each id in a table

GlobaLevel
GlobaLevel used Ask the Experts™
on
I have a unique id on each row of the table and I need to loop thru the whole table as it loops do some code

DECLARE @COUNT_BSP_ID NVARCHAR(MAX)
DECLARE @COUNTER INT

-- FIND HOW MANY BSP_ID IN TABLE
SET @COUNT_PARLAY_ID = (SELECT COUNT(PARLAY_ID) FROM PARLAY_NFL WHERE BSP_ID = @BSP_ID)
SET @COUNTER = 0



-- LOOP THRU TABLE DO CODE FOR EACH PARLAY_ID ON TABLE.. WHICH IS UNIQUE...
WHILE (@COUNTER < @COUNT_PARLAY_ID)
BEGIN

--DO SOME CODE PER EACH UNIQUE PARLAY_ID ON TABLE

 SET @COUNTER =  @COUNTER + 1

END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Here you go:

DECLARE @TableID INT
      

set @TableID=(
      SELECT MIN(@TableID)
      FROM Table
)

WHILE IS NOT NULL

      BEGIN                  
            
            CodeHere

            set @TableID = (
                  SELECT MIN(@TableID)
                  FROM Table
                  WHERE TableID >@TableID
            )

      END
It's best if you could use a set based solution, but if you have to loop, I would do something like this.

Greg


DECLARE @COUNTER INT
DECLARE @PARLAY_ID

-- PUT THE DATA IN A TEMP TABLE AND ASSIGN EACH RECORD AN ID
SELECT ROW_NUMBER() OVER (ORDER BY PARLAY_ID) AS ROWID, PARLAY_ID
INTO #PARLAY_NFL 
FROM PARLAY_NFL 
WHERE BSP_ID = @BSP_ID)

SET @COUNTER = 0

-- LOOP THRU TABLE DO CODE FOR EACH PARLAY_ID ON TABLE.. WHICH IS UNIQUE...
WHILE (@COUNTER <= (SELECT MAX(ROWID) FROM #PARLAY_NFL))
BEGIN

 --GET THE NEXT PARLAY_ID
 SELECT @PARLAY_ID = PARLAY_ID FROM #PARLAY_NFL WHERE ROWID = @COUNTER

--DO SOME CODE PER EACH UNIQUE PARLAY_ID ON TABLE

 SET @COUNTER =  @COUNTER + 1

END

Open in new window

GlobaLevelProgrammer

Author

Commented:
Greg...
not sure what you mean by set based?
Commented:
Declare @PARLAY_ID as int
DECLARE Testcursor SCROLL CURSOR FOR  
Select PARLAY_ID from Table1 Where WHERE BSP_ID = @BSP_ID
Group By [PARLAY_ID] -- So u will get unique records

OPEN Testcursor    
FETCH NEXT FROM Testcursor INTO    
PARLAY_ID

WHILE @@Fetch_Status = 0    -- will loop till last unique record
BEGIN
        --You can perform any operation for unique records here      
            FETCH NEXT FROM Testcursor INTO @PARLAY_ID
END