Is there a way to loop through a table variable in TSQL without using a cursor?

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

Although I'm not sure the problem you see with the above approach; See if this helps.. http://www.databasejournal.com/features/mssql/article.php/3111031

Answer 2

You can use a while loop:

While (Select Count(*) From #TempTable) > 0
Begin
    Insert Into @Databases...

    Delete From #TempTable Where x = x
End

Answer 3

First of all you should be absolutely sure you need to iterate through each row - set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop just using select statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

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

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

Answer 4

Here is how I would do it:

Select Identity(int, 1,1) AS PK, DatabaseID
Into   #T
From   @databases

Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    -- Get one record
    Select DatabaseID, Name, Server
    From @databases
    Where DatabaseID = (Select DatabaseID From #T Where PK = @pk)

    --Do some processing here
    -- 

    Select @pk = @pk + 1
End

---

[Edit] Because I probably skipped the word "variable" when I first time read the question, here is an updated response...

---

declare @databases table
(
    PK            int IDENTITY(1,1), 
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases
--/*
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MainDB', 'MyServer'
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MyDB',   'MyServer2'
--*/

Declare @maxPK int;Select @maxPK = MAX(PK) From @databases
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    /* Get one record (you can read the values into some variables) */
    Select DatabaseID, Name, Server
    From @databases
    Where PK = @pk

    /* Do some processing here */
    /* ... */ 

    Select @pk = @pk + 1
End

Answer 5

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 6

Just a quick note, if you are using SQL Server, 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 7

If you have no choice than to go row by row creating a FAST_FORWARD cursor. It will be as fast as building up a while loop and much easier to maintain over the long haul.

FAST_FORWARD Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Answer 8

Define your temp table like this -

declare @databases table
(
    RowID not null int identity(1,1) primary key,
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

Then do this -

declare @i int
select @i = min(RowID) from @databases
declare @max int
select @max = max(RowID) from @databases

while @i <= @max begin
    select DatabaseID, Name, Server from @database where RowID = @i --do some stuff
    set @i = @i + 1
end

Answer 9

I really do not see the point why you would need to resort to using dreaded cursor. But here is another option if you are using SQL Server version 2005/2008
Use Recursion

declare @databases table
(
 DatabaseID    int,
 Name        varchar(15),   
 Server      varchar(15)
)

--; Insert records into @databases...

--; Recurse through @databases
;with DBs as (
 select * from @databases where DatabaseID = 1
 union all
 select A.* from @databases A 
  inner join DBs B on A.DatabaseID = B.DatabaseID + 1
)
select * from DBs

Answer 10

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 11

I'm going to provide the set-based solution.

insert  @databases (DatabaseID, Name, Server)
select DatabaseID, Name, Server 
From ... (Use whatever query you would have used in the loop or cursor)

This is far faster than any looping techique and is easier to write and maintain.