Loop through all the rows of a temp table and call a stored procedure for each row

Question

I have declared a temp table to hold all the required values as follows      DECLARE  temp TABLE           Password INT      IdTran INT      Kind VARCHAR 16         INSERT INTO  temp SELECT  s Password  s IdTran   test  from signal s inner join vefify v      on s Password   v Password  and s IdTran   v IdTran  and v type    DEV   where s  Type     start   AND NOT EXISTS  SELECT   FROM signal s2             WHERE s Password   s2 Password              and s IdTran   s2 IdTran              and s2  Type     progress     INSERT INTO  temp SELECT  s Password  s IdTran   test  FROM signal s inner join vefify v      on s Password   v Password  and s IdTran   v IdTran  and v type    PROD   where s  Type     progress   AND NOT EXISTS  SELECT   FROM signal s2             WHERE s Password   s2 Password              and s IdTran   s2 IdTran              and s2  Type     finish                                            Now i need to loop through the rows in the  temp table and and for each row call a sp that takes all the parameters of  temp table as input  How can I achieve this

User · Answer

You always don t need a cursor for this  You can do it with a while loop  You should avoid cursors whenever possible  While loop is faster than cursors

User · Answer

Try returning the dataset from your stored procedure to your datatable in C  or VB Net   Then the large amount of data in your datatable can be copied to your destination table using a Bulk Copy   I have used BulkCopy for loading large datatables with thousands of rows  into Sql tables with great success in terms of performance   You may want to experiment with BulkCopy in your C  or VB Net code

User · Answer

you could use a cursor   DECLARE  id int DECLARE  pass varchar 100   DECLARE cur CURSOR FOR SELECT Id  Password FROM  temp OPEN cur  FETCH NEXT FROM cur INTO  id   pass  WHILE   FETCH STATUS   0 BEGIN     EXEC mysp  id   pass     -- call your sp here     FETCH NEXT FROM cur INTO  id   pass END  CLOSE cur     DEALLOCATE cur

User · Answer

something like this       DECLARE maxval  val   ind INT  SELECT MAX ID  as maxval FROM table   while  ind  lt   maxval    DO                   select  value  as val from  table  where  ID  ind         CALL fn val          SET ind   ind 1  end while

User · Answer

You can do something like this      Declare  min int 0   max int  0 --Initialize variable here which will be use in loop      Declare  Recordid int  TO nvarchar 30   Subject nvarchar 250   Body nvarchar max   --Initialize variable here which are useful for your      select ROW NUMBER   OVER ORDER BY  Recordid     AS Rownumber  Recordid   To    Subject    Body    Flag              into  temp Mail Mstr FROM Mail Mstr where Flag  1   --select your condition with row number  amp  get into a temp table      set  min    select MIN Rownumber  from  temp Mail Mstr   --Get minimum row number from temp table     set  max    select Max Rownumber  from  temp Mail Mstr    --Get maximum row number from temp table     while  min  lt    max     BEGIN         select  Recordid Recordid   To  To    Subject  Subject    Body Body from  temp Mail Mstr where Rownumber  min          -- You can use your variables  like  Recordid  To  Subject  Body  here           -- Do your work here           set  min  min 1 --Increment of current row number     END

[sql] Loop through all the rows of a temp table and call a stored procedure for each row

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures

Examples related to foreach