TSQL query to use While loop iteratively to replace cursor

Question

I have the following code where I am trying to replace the cursor where i want to iteratively go though each fileid one by one and assign the output of that single fileid each time to the variable @fil, but its printing everything for all the iterations. Explored many forums, but couldn't find the solution. I know the solution in cursor but this is new to me!

DECLARE @i INT = 0;
DECLARE @count INT, @fil varchar(100) 
SELECT @count=  COUNT(DISTINCT m.FileID)
FROM [EDW].[FileMaster] m 

WHILE @i <= @count
BEGIN
       
    @filid = SELECT DISTINCT m.FileID 
FROM [EDW].[FileMaster] m
    --OFFSET @i ROWS   
    --FETCH NEXT 1 ROWS ONLY  
    SET @i = @i + 1;
print @fileid
END

Expected output is abc.txt def.txt ghi.txt etc

Please help me! Thanks in advance.

Answer 1

You can try this, but you're missing the whole point of SQL...

DECLARE @i INT = 0; 
DECLARE @count INT = COUNT(DISTINCT FileID) FROM [EDW].[FileMaster]);
DECLARE @fil varchar(100);

WHILE @i <= @count 
BEGIN 
SET @fil = SELECT DISTINCT FileID FROM [EDW].[FileMaster] 
ORDER BY FileID OFFSET @i ROWS FETCH NEXT 1 ROWS ONLY
SET @i = @i + 1; 
print @fil 
END;

You can create a temp table with distinct FileIDs once and then cycle through it