ms sql server - loop thru each id in a table
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
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
It's best if you could use a set based solution, but if you have to loop, I would do something like this.
Greg
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
ENDASKER
Greg...
not sure what you mean by set based?
not sure what you mean by set based?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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