Based on http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx with a minor change.

DECLARE @CustomerID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
– declare cursor called ActiveCustomers
DECLARE ActiveCustomers Cursor FOR
 SELECT CustomerID, FirstName, LastName 
 FROM Customer
 WHERE Active = 1
– Open the cursor
OPEN ActiveCustomers
– Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
– perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
 Exec MyStoredProc @CustomerID, @Forename, @Surname
 – get next row of cursor
 FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName 
END
– Close the cursor to release locks
CLOSE ActiveCustomers
– Free memory used by cursor
DEALLOCATE ActiveCustomers

 
Cursor alternative 1: Using the SQL WHILE loop
SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:
– Create a temporary table, note the IDENTITY
– column that will be used to loop through
– the rows of this table

DECLARE @TempCustomer TABLE (
 RowID int IDENTITY(1, 1), 
 CustomerID int,
 FirstName varchar(30),
 LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)
– Insert the resultset we want to loop through
– into the temporary table
INSERT INTO @TempCustomer (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1 
 
– Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
 
– loop through all records in the temporary table
– using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName 
 FROM @TempCustomer 
 WHERE RowID = @RowCount
 EXEC MyStoredProc @CustomerID, @FirstName, @LastName
 SET @RowCount = @RowCount + 1
END