T-SQL loop over query results

Question

I run a query select  id table id from table and I need to loop over the results so I can exec a store procedure for each row exec stored proc  varName  id  otherVarName  test   How can I do this in a T-SQL script

User · Answer

try this   declare  i tinyint   0       count tinyint       id int       name varchar max   select  count   count    from table while   i  lt   count  begin     select  id   id   name   name from table     order by nr asc offset  i rows fetch next 1 rows only      exec stored proc  varName    id   otherVarName    test    varForName    name      set  i    i   1 end

User · Answer

You could do something like this   create procedure test as BEGIN      create table  ids               rn int          id int            insert into  ids  rn  id      select distinct row number   over order by id  as rn  id     from table      declare  id int     declare  totalrows int    select count    from  ids      declare  currentrow int   0      while  currentrow  lt    totalrows       begin          set  id    select id from  ids where rn    currentrow           exec stored proc  varName  id   otherVarName  test           set  currentrow    currentrow  1     end    END

User · Answer

My prefer solution is Microsoft KB 111401 http   support microsoft com kb 111401   The link refers to 3 examples   This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure  trigger  or Transact-SQL batch                 example 1                declare  au id char  11    set rowcount 0 select   into  mytemp from authors  set rowcount 1  select  au id   au id from  mytemp  while   rowcount  lt  gt  0 begin     set rowcount 0     select   from  mytemp where au id    au id     delete  mytemp where au id    au id      set rowcount 1     select  au id   au id from  mytemp end set rowcount 0                example 2               declare  au id char  11    select  au id   min  au id   from authors  while  au id is not null begin     select   from authors where au id    au id     select  au id   min  au id   from authors where au id  gt   au id end                example 3               set rowcount 0 select NULL mykey    into  mytemp from authors  set rowcount 1 update  mytemp set mykey   1  while   rowcount  gt  0 begin     set rowcount 0     select   from  mytemp where mykey   1     delete  mytemp where mykey   1     set rowcount 1     update  mytemp set mykey   1 end set rowcount 0

User · Answer

You could use a CURSOR in this case   DECLARE  id INT DECLARE  name NVARCHAR 100  DECLARE  getid CURSOR  SET  getid   CURSOR FOR SELECT table id         table name FROM   table  OPEN  getid FETCH NEXT FROM  getid INTO  id   name WHILE   FETCH STATUS   0 BEGIN     EXEC stored proc  varName  id   otherVarName  test    varForName  name     FETCH NEXT     FROM  getid INTO  id   name END  CLOSE  getid DEALLOCATE  getid   Modified to show multiple parameters from the table

User · Answer

DECLARE  id INT DECLARE  name NVARCHAR 100  DECLARE  getid CURSOR  SET  getid   CURSOR FOR SELECT table id         table name FROM   table  WHILE 1 1 BEGIN      FETCH NEXT     FROM  getid INTO  id   name     IF   FETCH STATUS  lt  0 BREAK      EXEC stored proc  varName  id   otherVarName  test    varForName  name  END  CLOSE  getid DEALLOCATE  getid

User · Answer

DECLARE  id INT DECLARE  filename NVARCHAR 100  DECLARE  getid CURSOR  SET  getid   CURSOR FOR SELECT top 3 id  filename  FROM  table  OPEN  getid WHILE 1 1 BEGIN      FETCH NEXT     FROM  getid INTO  id   filename     IF   FETCH STATUS  lt  0 BREAK      print  id  END   CLOSE  getid DEALLOCATE  getid

[tsql] T-SQL loop over query results

Examples related to tsql