How to store array of values in SQL Server and iterate over it

Question

DECLARE @Versions table (id int);

INSERT INTO @Versions
    SELECT DISTINCT Version_Id 
    FROM dbo.values 
    WHERE CatId = (SELECT id FROM dbo.Category WHERE Name = 'Locations') 

SELECT * FROM @Versions --- returns 1,2

Up to here, I am able to fetch version's data and store it in table valued parameter versions. But now I need to iterate over it to perform some manipulations like

    WHILE(till @Versions has value )   -- Iterate till @Versions exhausts its value ,which will be ideally 1,2 then stop iteration
     BEGIN 
       -- Update Statements for each version 
     END 

How to specify condition that will run only till @Versions has value (incrementally - for Version 1 execute some update, then do same for for v2, then exit)

Also please suggest if there any better way to do this !

Answer 1

You can use ROW_NUMBER as the below:

;WITH CTE
AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowId FROM @Versions
)

DECLARE @Counter INT 
SELECT @Counter = COUNT(*) FROM CTE

WHILE(@Counter != 0)   -- Iterate till @Versions exhausts its value ,which will be ideally 1,2 then stop iteration
    BEGIN 

    -- Current Version
    SELECT * FROM CTE
    WHERE RowId = @Counter

    -- Update Statements for each version 

    SET @Counter -= 1

    END 

Or you can use EXISTS

WHILE(EXISTS(SELECT 1 FROM @Versions))   -- Iterate till @Versions exhausts its value ,which will be ideally 1,2 then stop iteration
 BEGIN 
   -- Update Statements for each version 

   -- After update operation
   DELETE FROM @Versions
   WHERE Id = @CurrentVersionId
 END