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
