Is there a way to loop through a table variable in

Question

Let's say I have the following simple table variable:

declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases

Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?

Answer 1

Just a quick note, if you are using SQL Server (2008 and above), the examples that have:

While (Select Count(*) From #Temp) > 0

Would be better served with

While EXISTS(SELECT * From #Temp)

The Count will have to touch every single row in the table, the EXISTS only needs to touch the first one.

Answer 2

Lightweight, without having to make extra tables, if you have an integer ID on the table

Declare @id int = 0, @anything nvarchar(max)
WHILE(1=1) BEGIN
  Select Top 1 @anything=[Anything],@id=@id+1 FROM Table WHERE ID>@id
  if(@@ROWCOUNT=0) break;

  --Process @anything

END

Answer 3

I agree with the previous post that set-based operations will typically perform better, but if you do need to iterate over the rows here's the approach I would take:

1. Add a new field to your table variable (Data Type Bit, default 0)
2. Insert your data
3. Select the Top 1 Row where fUsed = 0 (Note: fUsed is the name of the field in step 1)
4. Perform whatever processing you need to do
5. Update the record in your table variable by setting fUsed = 1 for the record

6. Select the next unused record from the table and repeat the process

   DECLARE @databases TABLE  
   (  
       DatabaseID  int,  
       Name        varchar(15),     
       Server      varchar(15),   
       fUsed       BIT DEFAULT 0  
   ) 
   
   -- insert a bunch rows into @databases
   
   DECLARE @DBID INT
   
   SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 
   
   WHILE @@ROWCOUNT <> 0 and @DBID IS NOT NULL  
   BEGIN  
       -- Perform your processing here  
   
       --Update the record to "used" 
   
       UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID  
   
       --Get the next record  
       SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0   
   END
   

Answer 4

This is how I do it:

declare @RowNum int, @CustId nchar(5), @Name1 nchar(25)

select @CustId=MAX(USERID) FROM UserIDs     --start with the highest ID
Select @RowNum = Count(*) From UserIDs      --get total number of records
WHILE @RowNum > 0                          --loop until no more records
BEGIN   
    select @Name1 = username1 from UserIDs where USERID= @CustID    --get other info from that row
    print cast(@RowNum as char(12)) + ' ' + @CustId + ' ' + @Name1  --do whatever

    select top 1 @CustId=USERID from UserIDs where USERID < @CustID order by USERID desc--get the next one
    set @RowNum = @RowNum - 1                               --decrease count
END

No Cursors, no temporary tables, no extra columns. The USERID column must be a unique integer, as most Primary Keys are.

Answer 5

Another approach without having to change your schema or using temp tables:

DECLARE @rowCount int = 0
  ,@currentRow int = 1
  ,@databaseID int
  ,@name varchar(15)
  ,@server varchar(15);

SELECT @rowCount = COUNT(*)
FROM @databases;

WHILE (@currentRow <= @rowCount)
BEGIN
  SELECT TOP 1
     @databaseID = rt.[DatabaseID]
    ,@name = rt.[Name]
    ,@server = rt.[Server]
  FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY t.[DatabaseID], t.[Name], t.[Server]
       ) AS [RowNumber]
      ,t.[DatabaseID]
      ,t.[Name]
      ,t.[Server]
    FROM @databases t
  ) rt
  WHERE rt.[RowNumber] = @currentRow;

  EXEC [your_stored_procedure] @databaseID, @name, @server;

  SET @currentRow = @currentRow + 1;
END

Answer 6

This will work in SQL SERVER 2012 version.

declare @Rowcount int 
select @Rowcount=count(*) from AddressTable;

while( @Rowcount>0)
  begin 
 select @Rowcount=@Rowcount-1;
 SELECT * FROM AddressTable order by AddressId desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;
end