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?

Answer 1

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