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?

Tatar

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

Could you provide us the reason why you want to iterate over the rows, other solution that don't require iteration might exists (and which are faster by a large margin in most cases) – Cassis

agree with pop... may not need a cursor depending on the situation. but theres no problem with using cursors if you need to – Sagerman

wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them – Primavera

I was reading about looping through a table variable with a cursor and came across this, is there a benefit to using the select, update iteration covered below instead of a cursor? – Agrapha

similar question here: #62467 – Perri

You don't state why you want to avoid a cursor. Be aware that a cursor might be the simplest way to iterate. You may have heard that cursors are 'bad', but it is really iteration over tables that is bad compared to set-based operations. If you can't avoid iteration, a cursor might be the best way. Locking is another problem with cursors, but that is not relevant when using a table variable. – Mesothorium

Using a cursor isn't your only option, but if you have no way to avoid a row-by-row approach, then it will be your best option. CURSORs are a built-in construct that are more efficient and less error-prone than doing your own silly WHILE loop. Most of the time you just need to use the STATIC option to remove the constant re-checking of base tables and the locking that are there by default and cause most people to mistakenly believe that CURSORs are evil. @Mesothorium very close: rechecking to see if the result row still exists + locking are the issues. And STATIC usually fixes that :-). – Hopfinger

This is super old, but I'll add this here just incase someone else stumbles across this in the future. Unless your table is really small, iterating over each row without the use of a cursor is probably a bad idea. Using the accepted answer below could result in taking several hours to complete where as using a cursor would complete in a few seconds. – Peltry

Answer 1

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 using just 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 Where Processed = 0

    --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.

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

Meris

"Select Top 1 @Id = Id From ATable" should be "Select Top 1 @Id = Id From ATable Where Processed = 0" – Sumptuary

If using SQL Server, see Josef's answer below for a small tweak to the above. – Liberia

Can you explain why this is better than using a cursor ? – Arteritis

Gave this one a downvote. Why should he avoid using a cursor? He's talking about iterating over table variable, not a traditional table. I don't believe the normal downsides of cursors applies here. If row-by-row processing is truly required (and as you point out he should be certain about that first) then using a cursor is a much better solution than the ones you describe here. – Archicarp

@Archicarp You are correct. And in fact, you can usually avoid those "normal downsides" by using the STATIC option which copies the result set to a temp table, and hence you are no longer locking or re-checking base tables :-). – Hopfinger

After reading this article (sqlperformance.com/2012/09/t-sql-queries/cursor-options) I tried testing the same query with a #temp table. The cursor solution was by far faster then any #temp table solution I tried. Yes, there are ways to avoid cursors but are they all good for performance? I highly doubt that. – Meador

Wouldn't it be more efficient to do WHILE EXISTS(SELECT Top 1 * FROM #Temp) instead of SELECT * so that you're not reading the whole table every time just to see if the top row exists? – Stagg

Answer 2

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.

Accountancy

This is not an answer but a comment/enhancement on Martynw answer. – Wavelet

The content of this note forces better formatting functionality than a comment, I would suggest to append at the Answer. – Jair

In later versions of SQL, the query optimiser is clever enough to know that when you write the first thing, you actually mean the second and optimises it as such to avoid the table scan. – Shantishantung

Answer 3

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.

Landlocked

Good solution, for me the better approach was to use Min(Id) because my Id column had an ascending clustered index, So it was an extra operation for the queries to reverse the ordering. – Chrysalid

This won't work if your primary key is a UNIQUEIDENTIFER – Photoflood

Answer 4

Define your temp table like this -

declare @databases table
(
    RowID int not null 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

Emend

Answer 5

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

Barbwire

so basically youre doing a cursor, but without all of the benefits of a cursor – Sagerman

... without locking the tables that are used while processing... as this is one of the benefits of a cursor :) – Barbwire

Tables? It's a table VARIABLE - there is no concurrent access possible. – Communicate

DenNukem, you're right, I think I "skipped" the word "variable" when I read the question at that time... I will add some notes to the my initial response – Barbwire

I have to agree with DenNukem and Shawn. Why, why, why do you go to these lengths to avoid using a cursor? Again: he wants to iterate over a table variable, not a traditional table !!! – Archicarp

@nolan6000 - All of you commenting here are right, but read the question again. The user specifically asked about how to "loop through a table variable without a cursor" and if the cursor is his only option. I think my answer "answers" the question. – Barbwire

Ahh, I see your point. But why help him to a sub-optimal solution when a cursor is actually the optimal solution IN THIS PARTICULAR CASE. I'm thinking the user - like so many others - has been told to always avoid cursors but this is a too simplistic teaching when it comes to table variables, IMHO. – Archicarp

@nolan6000 - I think part of using this website properly is to learn to ask the right question... I usually answer a question, I don't try to change the question :) – Barbwire

Answer 6

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.

Enfleurage

Yeah! As I commented elsewhere I have yet to see any arguments as to why NOT to use a cursor when the case is to iterate over a table variable. A FAST_FORWARDcursor is a fine solution. (upvote) – Archicarp

Answer 7

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 

Kos

Answer 8

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

Riccardo

Answer 9

You can use a while loop:

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

    Delete From #TempTable Where x = x
End

Butylene

Answer 10

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

Ares

Answer 11

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

Imbibe

Answer 12

-- [PO_RollBackOnReject]  'FININV10532'
alter procedure PO_RollBackOnReject
@CaseID nvarchar(100)

AS
Begin
SELECT  *
INTO    #tmpTable
FROM   PO_InvoiceItems where CaseID = @CaseID

Declare @Id int
Declare @PO_No int
Declare @Current_Balance Money


While (Select ROW_NUMBER() OVER(ORDER BY PO_LineNo DESC) From #tmpTable) > 0
Begin
        Select Top 1 @Id = PO_LineNo, @Current_Balance = Current_Balance,
        @PO_No = PO_No
        From #Temp
        update PO_Details
        Set  Current_Balance = Current_Balance + @Current_Balance,
            Previous_App_Amount= Previous_App_Amount + @Current_Balance,
            Is_Processed = 0
        Where PO_LineNumber = @Id
        AND PO_No = @PO_No
        update PO_InvoiceItems
        Set IsVisible = 0,
        Is_Processed= 0
        ,Is_InProgress = 0 , 
        Is_Active = 0
        Where PO_LineNo = @Id
        AND PO_No = @PO_No
End
End

Thirteenth

Answer 13

It's possible to use a cursor to do this:

create function [dbo].f_teste_loop returns @tabela table ( cod int, nome varchar(10) ) as begin

insert into @tabela values (1, 'verde');
insert into @tabela values (2, 'amarelo');
insert into @tabela values (3, 'azul');
insert into @tabela values (4, 'branco');

return;

end

create procedure [dbo].[sp_teste_loop] as begin

DECLARE @cod int, @nome varchar(10);

DECLARE curLoop CURSOR STATIC LOCAL 
FOR
SELECT  
    cod
   ,nome
FROM 
    dbo.f_teste_loop();

OPEN curLoop;

FETCH NEXT FROM curLoop
           INTO @cod, @nome;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT @nome;

    FETCH NEXT FROM curLoop
           INTO @cod, @nome;
END

CLOSE curLoop;
DEALLOCATE curLoop;

end

Mcilroy

Wasn't the original question "Without using a cursor"? – Tertias

Answer 14

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.

Primavera

Answer 15

I prefer using the Offset Fetch if you have a unique ID you can sort your table by:

DECLARE @TableVariable (ID int, Name varchar(50));
DECLARE @RecordCount int;
SELECT @RecordCount = COUNT(*) FROM @TableVariable;

WHILE @RecordCount > 0
BEGIN
SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;
SET @RecordCount = @RecordCount - 1;
END

This way I don't need to add fields to the table or use a window function.

Athene

Answer 16

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
   

Ovaritis

Answer 17

Step1: Below select statement creates a temp table with unique row number for each record.

select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp 

Step2:Declare required variables

DECLARE @ROWNUMBER INT
DECLARE @ename varchar(100)

Step3: Take total rows count from temp table

SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri
declare @rno int

Step4: Loop temp table based on unique row number create in temp

while @rownumber>0
begin
  set @rno=@rownumber
  select @ename=ename from #tmp_sri where rno=@rno  **// You can take columns data from here as many as you want**
  set @rownumber=@rownumber-1
  print @ename **// instead of printing, you can write insert, update, delete statements**
end

Nessus

Answer 18

This approach only requires one variable and does not delete any rows from @databases. I know there are a lot of answers here, but I don't see one that uses MIN to get your next ID like this.

DECLARE @databases TABLE
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

DECLARE @CurrID INT

SELECT @CurrID = MIN(DatabaseID)
FROM @databases

WHILE @CurrID IS NOT NULL
BEGIN

    -- Do stuff for @CurrID

    SELECT @CurrID = MIN(DatabaseID)
    FROM @databases
    WHERE DatabaseID > @CurrID

END

Larry

Answer 19

Here's my solution, which makes use of an infinite loop, the BREAK statement, and the @@ROWCOUNT function. No cursors or temporary table are necessary, and I only need to write one query to get the next row in the @databases table:

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


-- Populate the [@databases] table with test data.
insert into @databases (DatabaseID, [Name], [Server])
select X.DatabaseID, X.[Name], X.[Server]
from (values 
    (1, 'Roger', 'ServerA'),
    (5, 'Suzy', 'ServerB'),
    (8675309, 'Jenny', 'TommyTutone')
) X (DatabaseID, [Name], [Server])


-- Create an infinite loop & ensure that a break condition is reached in the loop code.
declare @databaseId int;

while (1=1)
begin
    -- Get the next database ID.
    select top(1) @databaseId = DatabaseId 
    from @databases 
    where DatabaseId > isnull(@databaseId, 0);

    -- If no rows were found by the preceding SQL query, you're done; exit the WHILE loop.
    if (@@ROWCOUNT = 0) break;

    -- Otherwise, do whatever you need to do with the current [@databases] table row here.
    print 'Processing @databaseId #' + cast(@databaseId as varchar(50));
end

Peltier

I just realized that @ControlFreak recommended this approach before me; I simply added comments and a more verbose example. – Peltier

Answer 20

This is the code that I am using 2008 R2. This code that I am using is to build indexes on key fields (SSNO & EMPR_NO) n all tales

if object_ID('tempdb..#a')is not NULL drop table #a

select 'IF EXISTS (SELECT name FROM sysindexes WHERE name ='+CHAR(39)+''+'IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+char(39)+')' 
+' begin DROP INDEX [IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+'] ON '+table_schema+'.'+table_name+' END Create index IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+ ' on '+ table_schema+'.'+table_name+' ('+COLUMN_NAME+') '   'Field'
,ROW_NUMBER() over (order by table_NAMe) as  'ROWNMBR'
into #a
from INFORMATION_SCHEMA.COLUMNS
where (COLUMN_NAME like '%_SSNO_%' or COLUMN_NAME like'%_EMPR_NO_')
    and TABLE_SCHEMA='dbo'

declare @loopcntr int
declare @ROW int
declare @String nvarchar(1000)
set @loopcntr=(select count(*)  from #a)
set @ROW=1  

while (@ROW <= @loopcntr)
    begin
        select top 1 @String=a.Field 
        from #A a
        where a.ROWNMBR = @ROW
        execute sp_executesql @String
        set @ROW = @ROW + 1
    end 

Penitential

Answer 21

SELECT @pk = @pk + 1

would be better:

SET @pk += @pk

Avoid using SELECT if you are not referencing tables are are just assigning values.

Rhigolene