SQL Server loop - how do I loop through a set of records

Question

how do I loop through a set of records from a select   So say for example I have a few records that I wish to loop through and do something with each record  Here s a primitive version of my select   select top 1000   from dbo table where StatusID   7    Thanks

User · Answer

By using cursor you can easily iterate through records individually and print records separately or as a single message including all the records   DECLARE  CustomerID as INT  declare  msg varchar max  DECLARE  BusinessCursor as CURSOR   SET  BusinessCursor   CURSOR FOR SELECT CustomerID FROM Customer WHERE CustomerID IN   3908745   3911122   3911128   3911421    OPEN  BusinessCursor      FETCH NEXT FROM  BusinessCursor INTO  CustomerID      WHILE   FETCH STATUS   0         BEGIN             SET  msg                     CustomerID      CONVERT varchar 10    CustomerID                     Customer                      LastName    LastName-  CONVERT varchar 10    CustomerID                        FirstName    FirstName-  CONVERT varchar 10    CustomerID                                                  print  msg     FETCH NEXT FROM  BusinessCursor INTO  CustomerID  END

User · Answer

this way we can iterate into table data   DECLARE   MinJobID INT DECLARE   MaxJobID INT CREATE  TABLE  Temp  JobID INT   INSERT INTO  Temp SELECT   FROM DBO STRINGTOTABLE  JobID      SELECT   MinJID   MIN JobID    MaxJID   MAX JobID   FROM  Temp      WHILE   MinJID  lt     MaxJID     BEGIN          INSERT INTO Mytable                                       JobID                                     VALUES                                         MinJobID                              SET   MinJID     MinJID   1      END  DROP TABLE  Temp   STRINGTOTABLE is user define function which will parse comma separated data and return table  thanks

User · Answer

Just another approach if you are fine using temp tables I have personally tested this and it will not cause any exception  even if temp table does not have any data    CREATE TABLE  TempTable       ROWID int identity 1 1  primary key      HIERARCHY ID TO UPDATE int     --create some testing data --INSERT INTO  TempTable VALUES 1  --INSERT INTO  TempTable VALUES 2  --INSERT INTO  TempTable VALUES 4  --INSERT INTO  TempTable VALUES 6  --INSERT INTO  TempTable VALUES 8   DECLARE  MAXID INT   Counter INT  SET  COUNTER   1 SELECT  MAXID   COUNT    FROM  TempTable  WHILE   COUNTER  lt    MAXID  BEGIN     --DO THE PROCESSING HERE      SELECT  HIERARCHY ID TO UPDATE   PT HIERARCHY ID TO UPDATE     FROM  TempTable AS PT     WHERE ROWID    COUNTER      SET  COUNTER    COUNTER   1 END   IF  OBJECT ID  tempdb   TempTable   IS NOT NULL  BEGIN     DROP TABLE  TempTable END

User · Answer

This is what I ve been doing if you need to do something iterative    but it would be wise to look for set operations first  Also  do not do this because you don t want to learn cursors  select top 1000 TableID into  ControlTable  from dbo table where StatusID   7  declare  TableID int  while exists  select   from  ControlTable  begin      select top 1  TableID   TableID     from  ControlTable     order by TableID asc      -- Do something with your TableID      delete  ControlTable     where TableID    TableID  end  drop table  ControlTable

User · Answer

By using T-SQL and cursors like this     DECLARE  MyCursor CURSOR  DECLARE  MyField YourFieldDataType  BEGIN     SET  MyCursor   CURSOR FOR     select top 1000 YourField from dbo table         where StatusID   7            OPEN  MyCursor      FETCH NEXT FROM  MyCursor      INTO  MyField      WHILE   FETCH STATUS   0     BEGIN                   YOUR ALGORITHM GOES HERE                   FETCH NEXT FROM  MyCursor        INTO  MyField      END        CLOSE  MyCursor       DEALLOCATE  MyCursor  END

User · Answer

Small change to sam yi s answer  for better readability    select top 1000 TableID into  ControlTable  from dbo table where StatusID   7  declare  TableID int  while exists  select   from  ControlTable  begin      select  TableID    select top 1 TableID                        from  ControlTable                        order by TableID asc       -- Do something with your TableID      delete  ControlTable     where TableID    TableID  end  drop table  ControlTable

User · Answer

I think this is the easy way example to iterate item   declare  cateid int select CateID into   TempTable  from Category where GroupID    STOCKLIST   while  select count    from  TempTable   gt  0 begin     select top 1  cateid   CateID from  TempTable     print  cateid       --DO SOMETHING HERE      delete  TempTable where CateID    cateid end  drop table  TempTable

User · Answer

You could choose to rank your data and add a ROW NUMBER and count down to zero while iterate your dataset   -- Get your dataset and rank your dataset by adding a new row number SELECT  TOP 1000 A    ROW NUMBER   OVER ORDER BY A ID DESC  AS ROW INTO  TEMPTABLE  FROM DBO TABLE AS A WHERE STATUSID   7   --Find the highest number to start with DECLARE  COUNTER INT    SELECT MAX ROW  FROM  TEMPTABLE   DECLARE  ROW INT   -- Loop true your data until you hit 0 WHILE   COUNTER    0  BEGIN      SELECT  ROW   ROW     FROM  TEMPTABLE     WHERE ROW    COUNTER     ORDER BY ROW DESC      --DO SOMTHING COOL        -- SET your counter to -1     SET  COUNTER    ROW -1 END  DROP TABLE  TEMPTABLE

[sql] SQL Server loop - how do I loop through a set of records

Examples related to sql

Examples related to sql-server