Avoid Using SQL Server Cursors

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.