<p>Let's say I have the following simple table variable:</p>

<pre><code>declare @databases table
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases
</code></pre>

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

[{'answer_id': 61981, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 2, 'selected': False, 'text': '<p>You can use a while loop:</p>\n\n<pre><code>While (Select Count(*) From #TempTable) &gt; 0\nBegin\n    Insert Into @Databases...\n\n    Delete From #TempTable Where x = x\nEnd\n</code></pre>\n'}, {'answer_id': 62130, 'author': 'Martynnw', 'author_id': 5466, 'author_profile': 'https://Stackoverflow.com/users/5466', 'pm_score': 10, 'selected': True, 'text': '<p>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.</p>\n\n<p>Depending on your data it may be possible to loop using just <code>SELECT</code> statements as shown below:</p>\n\n<pre><code>Declare @Id int\n\nWhile (Select Count(*) From ATable Where Processed = 0) &gt; 0\nBegin\n    Select Top 1 @Id = Id From ATable Where Processed = 0\n\n    --Do some processing here\n\n    Update ATable Set Processed = 1 Where Id = @Id \n\nEnd\n</code></pre>\n\n<p>Another alternative is to use a temporary table: </p>\n\n<pre><code>Select *\nInto   #Temp\nFrom   ATable\n\nDeclare @Id int\n\nWhile (Select Count(*) From #Temp) &gt; 0\nBegin\n\n    Select Top 1 @Id = Id From #Temp\n\n    --Do some processing here\n\n    Delete #Temp Where Id = @Id\n\nEnd\n</code></pre>\n\n<p>The option you should choose really depends on the structure and volume of your data.</p>\n\n<p><strong>Note:</strong> If you are using SQL Server you would be better served using:</p>\n\n<pre><code>WHILE EXISTS(SELECT * FROM #Temp)\n</code></pre>\n\n<p>Using <code>COUNT</code> will have to touch every single row in the table, the <code>EXISTS</code> only needs to touch the first one (see <a href="https://stackoverflow.com/a/65294/963542">Josef\'s answer</a> below).</p>\n'}, {'answer_id': 63031, 'author': 'leoinfo', 'author_id': 6948, 'author_profile': 'https://Stackoverflow.com/users/6948', 'pm_score': 4, 'selected': False, 'text': '<p>Here is how I would do it:</p>\n\n<pre><code>Select Identity(int, 1,1) AS PK, DatabaseID\nInto   #T\nFrom   @databases\n\nDeclare @maxPK int;Select @maxPK = MAX(PK) From #T\nDeclare @pk int;Set @pk = 1\n\nWhile @pk &lt;= @maxPK\nBegin\n\n    -- Get one record\n    Select DatabaseID, Name, Server\n    From @databases\n    Where DatabaseID = (Select DatabaseID From #T Where PK = @pk)\n\n    --Do some processing here\n    -- \n\n    Select @pk = @pk + 1\nEnd\n</code></pre>\n\n<hr>\n\n<p>[Edit] Because I probably skipped the word "variable" when I first time read the question, here is an updated response...</p>\n\n<hr>\n\n<pre><code>declare @databases table\n(\n    PK            int IDENTITY(1,1), \n    DatabaseID    int,\n    Name        varchar(15),   \n    Server      varchar(15)\n)\n-- insert a bunch rows into @databases\n--/*\nINSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,\'MainDB\', \'MyServer\'\nINSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,\'MyDB\',   \'MyServer2\'\n--*/\n\nDeclare @maxPK int;Select @maxPK = MAX(PK) From @databases\nDeclare @pk int;Set @pk = 1\n\nWhile @pk &lt;= @maxPK\nBegin\n\n    /* Get one record (you can read the values into some variables) */\n    Select DatabaseID, Name, Server\n    From @databases\n    Where PK = @pk\n\n    /* Do some processing here */\n    /* ... */ \n\n    Select @pk = @pk + 1\nEnd\n</code></pre>\n'}, {'answer_id': 63440, 'author': 'Tim Lentine', 'author_id': 2833, 'author_profile': 'https://Stackoverflow.com/users/2833', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n\n<ol>\n<li>Add a new field to your table variable (Data Type Bit, default 0)</li>\n<li>Insert your data</li>\n<li>Select the Top 1 Row where fUsed = 0 <em>(Note: fUsed is the name of the field in step 1)</em></li>\n<li>Perform whatever processing you need to do</li>\n<li>Update the record in your table variable by setting fUsed = 1 for the record</li>\n<li><p>Select the next unused record from the table and repeat the process</p>\n\n<pre><code>DECLARE @databases TABLE  \n(  \n    DatabaseID  int,  \n    Name        varchar(15),     \n    Server      varchar(15),   \n    fUsed       BIT DEFAULT 0  \n) \n\n-- insert a bunch rows into @databases\n\nDECLARE @DBID INT\n\nSELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 \n\nWHILE @@ROWCOUNT &lt;&gt; 0 and @DBID IS NOT NULL  \nBEGIN  \n    -- Perform your processing here  \n\n    --Update the record to "used" \n\n    UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID  \n\n    --Get the next record  \n    SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0   \nEND\n</code></pre></li>\n</ol>\n'}, {'answer_id': 65294, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 7, 'selected': False, 'text': '<p>Just a quick note, if you are using SQL Server (2008 and above), the examples that have:</p>\n\n<pre><code>While (Select Count(*) From #Temp) &gt; 0\n</code></pre>\n\n<p>Would be better served with </p>\n\n<pre><code>While EXISTS(SELECT * From #Temp)\n</code></pre>\n\n<p>The Count will have to touch every single row in the table, the <code>EXISTS</code> only needs to touch the first one.</p>\n'}, {'answer_id': 77062, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<p>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.</p>\n\n<p>FAST_FORWARD\n    Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. </p>\n'}, {'answer_id': 77601, 'author': 'Seibar', 'author_id': 357, 'author_profile': 'https://Stackoverflow.com/users/357', 'pm_score': 5, 'selected': False, 'text': '<p>Define your temp table like this -</p>\n\n<pre><code>declare @databases table\n(\n    RowID int not null identity(1,1) primary key,\n    DatabaseID    int,\n    Name        varchar(15),   \n    Server      varchar(15)\n)\n\n-- insert a bunch rows into @databases\n</code></pre>\n\n<p>Then do this -</p>\n\n<pre><code>declare @i int\nselect @i = min(RowID) from @databases\ndeclare @max int\nselect @max = max(RowID) from @databases\n\nwhile @i &lt;= @max begin\n    select DatabaseID, Name, Server from @database where RowID = @i --do some stuff\n    set @i = @i + 1\nend\n</code></pre>\n'}, {'answer_id': 680272, 'author': 'dance2die', 'author_id': 4035, 'author_profile': 'https://Stackoverflow.com/users/4035', 'pm_score': 2, 'selected': False, 'text': '<p>I really do not see the point why you would need to resort to using dreaded <code>cursor</code>.\nBut here is another option if you are using SQL Server version 2005/2008\n<br />\nUse <strong>Recursion</strong></p>\n\n<pre><code>declare @databases table\n(\n    DatabaseID    int,\n    Name        varchar(15),   \n    Server      varchar(15)\n)\n\n--; Insert records into @databases...\n\n--; Recurse through @databases\n;with DBs as (\n    select * from @databases where DatabaseID = 1\n    union all\n    select A.* from @databases A \n        inner join DBs B on A.DatabaseID = B.DatabaseID + 1\n)\nselect * from DBs\n</code></pre>\n'}, {'answer_id': 2084732, 'author': 'Trevor', 'author_id': 253034, 'author_profile': 'https://Stackoverflow.com/users/253034', 'pm_score': 6, 'selected': False, 'text': "<p>This is how I do it:</p>\n\n<pre><code>declare @RowNum int, @CustId nchar(5), @Name1 nchar(25)\n\nselect @CustId=MAX(USERID) FROM UserIDs     --start with the highest ID\nSelect @RowNum = Count(*) From UserIDs      --get total number of records\nWHILE @RowNum &gt; 0                          --loop until no more records\nBEGIN   \n    select @Name1 = username1 from UserIDs where USERID= @CustID    --get other info from that row\n    print cast(@RowNum as char(12)) + ' ' + @CustId + ' ' + @Name1  --do whatever\n\n    select top 1 @CustId=USERID from UserIDs where USERID &lt; @CustID order by USERID desc--get the next one\n    set @RowNum = @RowNum - 1                               --decrease count\nEND\n</code></pre>\n\n<p>No Cursors, no temporary tables, no extra columns.\nThe USERID column must be a unique integer, as most Primary Keys are.</p>\n"}, {'answer_id': 2135015, 'author': 'HLGEM', 'author_id': 9034, 'author_profile': 'https://Stackoverflow.com/users/9034', 'pm_score': 2, 'selected': False, 'text': "<p>I'm going to provide the set-based solution.</p>\n\n<pre><code>insert  @databases (DatabaseID, Name, Server)\nselect DatabaseID, Name, Server \nFrom ... (Use whatever query you would have used in the loop or cursor)\n</code></pre>\n\n<p>This is far faster than any looping techique and is easier to write and maintain. </p>\n"}, {'answer_id': 10371631, 'author': 'Syed Umar Ahmed', 'author_id': 861216, 'author_profile': 'https://Stackoverflow.com/users/861216', 'pm_score': 2, 'selected': False, 'text': "<pre><code>-- [PO_RollBackOnReject]  'FININV10532'\nalter procedure PO_RollBackOnReject\n@CaseID nvarchar(100)\n\nAS\nBegin\nSELECT  *\nINTO    #tmpTable\nFROM   PO_InvoiceItems where CaseID = @CaseID\n\nDeclare @Id int\nDeclare @PO_No int\nDeclare @Current_Balance Money\n\n\nWhile (Select ROW_NUMBER() OVER(ORDER BY PO_LineNo DESC) From #tmpTable) &gt; 0\nBegin\n        Select Top 1 @Id = PO_LineNo, @Current_Balance = Current_Balance,\n        @PO_No = PO_No\n        From #Temp\n        update PO_Details\n        Set  Current_Balance = Current_Balance + @Current_Balance,\n            Previous_App_Amount= Previous_App_Amount + @Current_Balance,\n            Is_Processed = 0\n        Where PO_LineNumber = @Id\n        AND PO_No = @PO_No\n        update PO_InvoiceItems\n        Set IsVisible = 0,\n        Is_Processed= 0\n        ,Is_InProgress = 0 , \n        Is_Active = 0\n        Where PO_LineNo = @Id\n        AND PO_No = @PO_No\nEnd\nEnd\n</code></pre>\n"}, {'answer_id': 17713828, 'author': 'SReiderB', 'author_id': 2571174, 'author_profile': 'https://Stackoverflow.com/users/2571174', 'pm_score': 3, 'selected': False, 'text': '<p>Another approach without having to change your schema or using temp tables:</p>\n\n<pre><code>DECLARE @rowCount int = 0\n  ,@currentRow int = 1\n  ,@databaseID int\n  ,@name varchar(15)\n  ,@server varchar(15);\n\nSELECT @rowCount = COUNT(*)\nFROM @databases;\n\nWHILE (@currentRow &lt;= @rowCount)\nBEGIN\n  SELECT TOP 1\n     @databaseID = rt.[DatabaseID]\n    ,@name = rt.[Name]\n    ,@server = rt.[Server]\n  FROM (\n    SELECT ROW_NUMBER() OVER (\n        ORDER BY t.[DatabaseID], t.[Name], t.[Server]\n       ) AS [RowNumber]\n      ,t.[DatabaseID]\n      ,t.[Name]\n      ,t.[Server]\n    FROM @databases t\n  ) rt\n  WHERE rt.[RowNumber] = @currentRow;\n\n  EXEC [your_stored_procedure] @databaseID, @name, @server;\n\n  SET @currentRow = @currentRow + 1;\nEND\n</code></pre>\n'}, {'answer_id': 23432301, 'author': 'OrganicCoder', 'author_id': 2611808, 'author_profile': 'https://Stackoverflow.com/users/2611808', 'pm_score': 3, 'selected': False, 'text': '<p>This will work in SQL SERVER 2012 version.</p>\n\n<pre><code>declare @Rowcount int \nselect @Rowcount=count(*) from AddressTable;\n\nwhile( @Rowcount&gt;0)\n  begin \n select @Rowcount=@Rowcount-1;\n SELECT * FROM AddressTable order by AddressId desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;\nend \n</code></pre>\n'}, {'answer_id': 23524660, 'author': 'howmnsk', 'author_id': 3613336, 'author_profile': 'https://Stackoverflow.com/users/3613336', 'pm_score': 0, 'selected': False, 'text': "<p>This is the code that I am using 2008 R2. This code that I am using is to build indexes on key fields (SSNO &amp; EMPR_NO) n all tales</p>\n\n<pre><code>if object_ID('tempdb..#a')is not NULL drop table #a\n\nselect 'IF EXISTS (SELECT name FROM sysindexes WHERE name ='+CHAR(39)+''+'IDX_'+COLUMN_NAME+'_'+SUBSTRING(table_name,5,len(table_name)-3)+char(39)+')' \n+' 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'\n,ROW_NUMBER() over (order by table_NAMe) as  'ROWNMBR'\ninto #a\nfrom INFORMATION_SCHEMA.COLUMNS\nwhere (COLUMN_NAME like '%_SSNO_%' or COLUMN_NAME like'%_EMPR_NO_')\n    and TABLE_SCHEMA='dbo'\n\ndeclare @loopcntr int\ndeclare @ROW int\ndeclare @String nvarchar(1000)\nset @loopcntr=(select count(*)  from #a)\nset @ROW=1  \n\nwhile (@ROW &lt;= @loopcntr)\n    begin\n        select top 1 @String=a.Field \n        from #A a\n        where a.ROWNMBR = @ROW\n        execute sp_executesql @String\n        set @ROW = @ROW + 1\n    end \n</code></pre>\n"}, {'answer_id': 23768656, 'author': 'Bob Alley', 'author_id': 3658181, 'author_profile': 'https://Stackoverflow.com/users/3658181', 'pm_score': 0, 'selected': False, 'text': '<pre><code>SELECT @pk = @pk + 1\n</code></pre>\n\n<p>would be better: </p>\n\n<pre><code>SET @pk += @pk\n</code></pre>\n\n<p>Avoid using SELECT if you are not referencing tables are are just assigning values.</p>\n'}, {'answer_id': 25618719, 'author': 'Srinivas Maale', 'author_id': 3999541, 'author_profile': 'https://Stackoverflow.com/users/3999541', 'pm_score': 1, 'selected': False, 'text': '<p>Step1: Below select statement creates a temp table with unique row number for each record.</p>\n\n<pre><code>select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp \n</code></pre>\n\n<p>Step2:Declare required variables</p>\n\n<pre><code>DECLARE @ROWNUMBER INT\nDECLARE @ename varchar(100)\n</code></pre>\n\n<p>Step3: Take total rows count from temp table</p>\n\n<pre><code>SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri\ndeclare @rno int\n</code></pre>\n\n<p>Step4: Loop temp table based on unique row number create in temp</p>\n\n<pre><code>while @rownumber&gt;0\nbegin\n  set @rno=@rownumber\n  select @ename=ename from #tmp_sri where rno=@rno  **// You can take columns data from here as many as you want**\n  set @rownumber=@rownumber-1\n  print @ename **// instead of printing, you can write insert, update, delete statements**\nend\n</code></pre>\n'}, {'answer_id': 37035265, 'author': 'Control Freak', 'author_id': 916535, 'author_profile': 'https://Stackoverflow.com/users/916535', 'pm_score': 2, 'selected': False, 'text': '<p>Lightweight, without having to make extra tables, if you have an integer <code>ID</code> on the table</p>\n\n<pre><code>Declare @id int = 0, @anything nvarchar(max)\nWHILE(1=1) BEGIN\n  Select Top 1 @anything=[Anything],@id=@id+1 FROM Table WHERE ID&gt;@id\n  if(@@ROWCOUNT=0) break;\n\n  --Process @anything\n\nEND\n</code></pre>\n'}, {'answer_id': 37867029, 'author': 'Sean', 'author_id': 240430, 'author_profile': 'https://Stackoverflow.com/users/240430', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<pre><code>DECLARE @databases TABLE\n(\n    DatabaseID    int,\n    Name        varchar(15),   \n    Server      varchar(15)\n)\n\n-- insert a bunch rows into @databases\n\nDECLARE @CurrID INT\n\nSELECT @CurrID = MIN(DatabaseID)\nFROM @databases\n\nWHILE @CurrID IS NOT NULL\nBEGIN\n\n    -- Do stuff for @CurrID\n\n    SELECT @CurrID = MIN(DatabaseID)\n    FROM @databases\n    WHERE DatabaseID &gt; @CurrID\n\nEND\n</code></pre>\n"}, {'answer_id': 40109813, 'author': 'Yves A Martin', 'author_id': 1579352, 'author_profile': 'https://Stackoverflow.com/users/1579352', 'pm_score': 2, 'selected': False, 'text': "<p>I prefer using the Offset Fetch if you have a unique ID you can sort your table by:</p>\n\n<pre><code>DECLARE @TableVariable (ID int, Name varchar(50));\nDECLARE @RecordCount int;\nSELECT @RecordCount = COUNT(*) FROM @TableVariable;\n\nWHILE @RecordCount &gt; 0\nBEGIN\nSELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;\nSET @RecordCount = @RecordCount - 1;\nEND\n</code></pre>\n\n<p>This way I don't need to add fields to the table or use a window function.</p>\n"}, {'answer_id': 42760525, 'author': 'Alexandre Pezzutto', 'author_id': 7702229, 'author_profile': 'https://Stackoverflow.com/users/7702229', 'pm_score': 2, 'selected': False, 'text': "<p>It's possible to use a cursor to do this:</p>\n\n<p>create function [dbo].f_teste_loop\nreturns @tabela table\n(\n    cod  int,\n    nome varchar(10)\n)\nas \nbegin</p>\n\n<pre><code>insert into @tabela values (1, 'verde');\ninsert into @tabela values (2, 'amarelo');\ninsert into @tabela values (3, 'azul');\ninsert into @tabela values (4, 'branco');\n\nreturn;\n</code></pre>\n\n<p>end</p>\n\n<p>create procedure [dbo].[sp_teste_loop]\nas\nbegin</p>\n\n<pre><code>DECLARE @cod int, @nome varchar(10);\n\nDECLARE curLoop CURSOR STATIC LOCAL \nFOR\nSELECT  \n    cod\n   ,nome\nFROM \n    dbo.f_teste_loop();\n\nOPEN curLoop;\n\nFETCH NEXT FROM curLoop\n           INTO @cod, @nome;\n\nWHILE (@@FETCH_STATUS = 0)\nBEGIN\n    PRINT @nome;\n\n    FETCH NEXT FROM curLoop\n           INTO @cod, @nome;\nEND\n\nCLOSE curLoop;\nDEALLOCATE curLoop;\n</code></pre>\n\n<p>end</p>\n"}, {'answer_id': 43052926, 'author': 'Mass Dot Net', 'author_id': 165494, 'author_profile': 'https://Stackoverflow.com/users/165494', 'pm_score': 1, 'selected': False, 'text': "<p>Here's my solution, which makes use of an infinite loop, the <code>BREAK</code> statement, and the <code>@@ROWCOUNT</code> function.   No cursors or temporary table are necessary, and I only need to write one query to get the next row in the <code>@databases</code> table:</p>\n\n<pre><code>declare @databases table\n(\n    DatabaseID    int,\n    [Name]        varchar(15),   \n    [Server]      varchar(15)\n);\n\n\n-- Populate the [@databases] table with test data.\ninsert into @databases (DatabaseID, [Name], [Server])\nselect X.DatabaseID, X.[Name], X.[Server]\nfrom (values \n    (1, 'Roger', 'ServerA'),\n    (5, 'Suzy', 'ServerB'),\n    (8675309, 'Jenny', 'TommyTutone')\n) X (DatabaseID, [Name], [Server])\n\n\n-- Create an infinite loop &amp; ensure that a break condition is reached in the loop code.\ndeclare @databaseId int;\n\nwhile (1=1)\nbegin\n    -- Get the next database ID.\n    select top(1) @databaseId = DatabaseId \n    from @databases \n    where DatabaseId &gt; isnull(@databaseId, 0);\n\n    -- If no rows were found by the preceding SQL query, you're done; exit the WHILE loop.\n    if (@@ROWCOUNT = 0) break;\n\n    -- Otherwise, do whatever you need to do with the current [@databases] table row here.\n    print 'Processing @databaseId #' + cast(@databaseId as varchar(50));\nend\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/61967', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4872/']