Can I loop through a table variable in T-SQL

Question

Is there anyway to loop through a table variable in T-SQL   DECLARE  table1 TABLE   col1 int     INSERT into  table1 SELECT col1 FROM table2   I use cursors as well  but cursors seem less flexible than table variables   DECLARE cursor1 CURSOR       FOR SELECT col1 FROM table2   OPEN cursor1   FETCH NEXT FROM cursor1   I would like to be able to use a table variable in the same manner as a cursor  That way I could execute some query on the table variable in one part of the procedure  and then later execute some code for each row in the table variable   Any help is greatly appreciated

User · Answer

Here s my variant  Pretty much just like all the others  but I only use one variable to manage the looping   DECLARE    LoopId  int    MyData  varchar 100   DECLARE  CheckThese TABLE       LoopId  int  not null  identity 1 1     MyData  varchar 100   not null      INSERT  CheckThese  MyData   select MyData from MyTable  order by DoesItMatter  SET  LoopId     rowcount  WHILE  LoopId  gt  0  BEGIN     SELECT  MyData   MyData      from  CheckThese      where LoopId    LoopId      --  Do whatever      SET  LoopId    LoopId - 1  END   Raj More s point is relevant--only perform loops if you have to

User · Answer

My two cents   From KM  s answer  if you want to drop one variable  you can do a countdown on  RowsToProcess instead of counting up   DECLARE  RowsToProcess  int   DECLARE  table1 TABLE  RowID int not null primary key identity 1 1   col1 int     INSERT into  table1  col1  SELECT col1 FROM table2 SET  RowsToProcess     ROWCOUNT   WHILE  RowsToProcess  gt  0 -- Countdown BEGIN     SELECT           FROM  table1         WHERE RowID  RowsToProcess      --do your thing here--      SET  RowsToProcess    RowsToProcess - 1  -- Countdown END

User · Answer

DECLARE  table1 TABLE       idx int identity 1 1       col1 int    DECLARE  counter int  SET  counter   1  WHILE  counter  lt  SELECT MAX idx  FROM  table1  BEGIN     DECLARE  colVar INT      SELECT  colVar   col1 FROM  table1 WHERE idx    counter      -- Do your work here      SET  counter    counter   1 END   Believe it or not  this is actually more efficient and performant than using a cursor

User · Answer

I didn t know about the WHILE structure   The WHILE structure with a table variable  however  looks similar to using a CURSOR  in that you still have to SELECT the row into a variable based on the row IDENTITY  which is effectively a FETCH   Is there any difference between using WHERE and something like the following   DECLARE  table1 TABLE   col1 int     INSERT into  table1 SELECT col1 FROM table2  DECLARE cursor1 CURSOR       FOR  table1 OPEN cursor1   FETCH NEXT FROM cursor1   I don t know if that s even possible  I suppose you might have to do this   DECLARE cursor1 CURSOR       FOR SELECT col1 FROM  table1 OPEN cursor1   FETCH NEXT FROM cursor1   Thanks for you help

User · Answer

Here is my version of the same solution         declare  id int          SELECT  id   min fPat PatientID          FROM tbPatients fPat         WHERE  fPat InsNotes is not null AND DataLength fPat InsNotes  gt 0   while  id is not null begin     SELECT fPat PatientID  fPat InsNotes     FROM tbPatients fPat     WHERE  fPat InsNotes is not null AND DataLength fPat InsNotes  gt 0  AND fPat PatientID  id      SELECT  id   min fPat PatientID      FROM tbPatients fPat     WHERE  fPat InsNotes is not null AND DataLength fPat InsNotes  gt 0 AND fPat PatientID gt  id  end

User · Answer

look like this demo   DECLARE  vTable TABLE  IdRow int not null primary key identity 1 1  ValueRow int    -------Initialize--------- insert into  vTable select 345  insert into  vTable select 795  insert into  vTable select 565  ---------------------------  DECLARE  cnt int   1  DECLARE  max int    SELECT MAX IdRow  FROM  vTable    WHILE  cnt  lt    max BEGIN     DECLARE  tempValueRow int    Select ValueRow FROM  vTable WHERE IdRow    cnt        ---work demo----     print   tempValueRow     convert varchar 10   tempValueRow       print   cnt     convert varchar 10   cnt       print        --------------      set  cnt    cnt 1  END   Version without idRow  using ROW NUMBER      DECLARE  vTable TABLE  ValueRow int   -------Initialize--------- insert into  vTable select 345  insert into  vTable select 795  insert into  vTable select 565  ---------------------------  DECLARE  cnt int   1  DECLARE  max int    select count    from  vTable    WHILE  cnt  lt    max BEGIN     DECLARE  tempValueRow int             select ValueRow          from  select ValueRow               ROW NUMBER   OVER ORDER BY  select 1   as RowId              from  vTable           T1      where t1 RowId    cnt             ---work demo----     print   tempValueRow     convert varchar 10   tempValueRow       print   cnt     convert varchar 10   cnt       print        --------------      set  cnt    cnt 1  END

User · Answer

Select Top 1 can easily resolve it without the need of any sequence order  Create Function Test Range   Returns  Result Table  ID Int  As Begin  Declare  ID Varchar 10       Declare  Rows Int   Row Int   0 Declare  Num Int   RangeTo Int  Declare  RangeTable Table  ID Varchar 10   RangeFrom Int  RangeTo Int  Insert Into  RangeTable Values   A   1  10  Insert Into  RangeTable Values   B   25 30   Set  Rows    Select Count    From  RangeTable   While  Row  lt    Rows Begin     Set  Row    Row   1     Select Top 1  ID   ID   Num   RangeFrom   RangeTo   RangeTo  From  RangeTable     Where ID  gt   ID     While  Num  lt    RangeTo     Begin         Insert Into  Result Values   Num          Set  Num    Num   1     End End Return End

User · Answer

Following Stored Procedure loop through the Table Variable and Prints it in Ascending ORDER  This example is using WHILE LOOP    CREATE PROCEDURE PrintSequenceSeries      -- Add the parameters for the stored procedure here      ComaSeperatedSequenceSeries nVarchar MAX    AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON       DECLARE  SERIES COUNT AS INTEGER     SELECT  SERIES COUNT   COUNT    FROM PARSE COMMA DELIMITED INTEGER  ComaSeperatedSequenceSeries        --- ORDER BY ITEM DESC      DECLARE  CURR COUNT AS INTEGER     SET  CURR COUNT   1      DECLARE  SQL AS NVARCHAR MAX       WHILE  CURR COUNT  lt    SERIES COUNT     BEGIN         SET  SQL    SELECT TOP 1 T   FROM                    SELECT TOP     CONVERT VARCHAR 20    CURR COUNT        FROM PARSE COMMA DELIMITED INTEGER         ComaSeperatedSequenceSeries                ORDER BY ITEM ASC  AS T                  ORDER BY T ITEM DESC           PRINT  SQL          EXEC SP EXECUTESQL  SQL          SET  CURR COUNT    CURR COUNT   1     END    Following Statement Executes the Stored Procedure   EXEC  PrintSequenceSeries  11 2 33 14 5 60 17 98 9 10    The result displayed in SQL Query window is shown below     The function PARSE COMMA DELIMITED INTEGER   that returns TABLE variable is as shown below    CREATE FUNCTION  dbo   parse comma delimited integer                         LIST       VARCHAR 8000                 DELIMITER  VARCHAR 10                                        -- TABLE VARIABLE THAT WILL CONTAIN VALUES         RETURNS  TABLEVALUES TABLE                        ITEM INT                   AS         BEGIN              DECLARE  ITEM VARCHAR 255                  LOOP OVER THE COMMADELIMITED LIST                WHILE  DATALENGTH  LIST   gt  0                  BEGIN                      IF CHARINDEX  DELIMITER  LIST   gt  0                         BEGIN                             SELECT  ITEM   SUBSTRING  LIST 1  CHARINDEX  DELIMITER   LIST -1                               SELECT  LIST    SUBSTRING  LIST  CHARINDEX  DELIMITER   LIST                                DATALENGTH  DELIMITER   DATALENGTH  LIST                           END                     ELSE                         BEGIN                             SELECT  ITEM    LIST                             SELECT  LIST   NULL                         END                      -- INSERT EACH ITEM INTO TEMP TABLE                     INSERT  TABLEVALUES                                                ITEM                                           SELECT ITEM   CONVERT INT   ITEM                   END         RETURN         END

User · Answer

Add an identity to your table variable  and do an easy loop from 1 to the   ROWCOUNT of the INSERT-SELECT   Try this   DECLARE  RowsToProcess  int DECLARE  CurrentRow     int DECLARE  SelectCol1     int  DECLARE  table1 TABLE  RowID int not null primary key identity 1 1   col1 int     INSERT into  table1  col1  SELECT col1 FROM table2 SET  RowsToProcess   ROWCOUNT  SET  CurrentRow 0 WHILE  CurrentRow lt  RowsToProcess BEGIN     SET  CurrentRow  CurrentRow 1     SELECT           SelectCol1 col1         FROM  table1         WHERE RowID  CurrentRow      --do your thing here--  END

User · Answer

Here s another answer  similar to Justin s  but doesn t need an identity or aggregate  just a primary  unique  key   declare  table1 table dataKey int  dataCol1 varchar 20   dataCol2 datetime  declare  dataKey int while exists select  x  from  table1 begin     select top 1  dataKey   dataKey      from  table1      order by   whatever you want    dataCol2 desc      -- do processing      delete from  table1 where dataKey    dataKey end

User · Answer

You can loop through the table variable or you can cursor through it   This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row   I would suggest finding a SET-BASED answer to your question  we can help with that  and move away from rbars as much as possible

[sql] Can I loop through a table variable in T-SQL?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to cursor

Examples related to table-variable