How can I iterate over a recordset within a stored procedure?

Question

I need to iterate over a recordset from a stored procedure and execute another stored procedure using each fields as arguments. I can't complete this iteration in the code. I have found samples on the internets, but they all seem to deal with a counter. I'm not sure if my problem involved a counter. I need the T-SQL equivalent of a foreach

Currently, my first stored procedure stores its recordset in a temp table, #mytemp. I assume I will call the secondary stored procedure like this:

while (something)     execute nameofstoredprocedure arg1, arg2, arg3 end 

Answer 1

You need to create a cursor to loop through the record set.

Example Table:

CREATE TABLE Customers (     CustomerId INT NOT NULL PRIMARY KEY IDENTITY(1,1)     ,FirstName Varchar(50)      ,LastName VARCHAR(40) )  INSERT INTO Customers VALUES('jane', 'doe') INSERT INTO Customers VALUES('bob', 'smith') 

Cursor:

DECLARE @CustomerId INT, @FirstName VARCHAR(30), @LastName VARCHAR(50)  DECLARE @MessageOutput VARCHAR(100)  DECLARE Customer_Cursor CURSOR FOR      SELECT CustomerId, FirstName, LastName FROM Customers   OPEN Customer_Cursor   FETCH NEXT FROM Customer_Cursor INTO     @CustomerId, @FirstName, @LastName  WHILE @@FETCH_STATUS = 0 BEGIN     SET @MessageOutput = @FirstName + ' ' + @LastName        RAISERROR(@MessageOutput,0,1) WITH NOWAIT      FETCH NEXT FROM Customer_Cursor INTO     @CustomerId, @FirstName, @LastName END CLOSE Customer_Cursor DEALLOCATE Customer_Cursor 

Here is a link to MSDN on how to create them.

http://msdn.microsoft.com/en-us/library/ms180169.aspx

This is why I used Raise Error instead of PRINT for output.
http://structuredsight.com/2014/11/24/wait-wait-dont-tell-me-on-second-thought/