Avoid Using SQL Server Cursors

Cursor
How Cursor works

SQL Server Cursor is a necessary evil. Its useful but at the same time its slow and time consuming when data set is large. So how to make it faster and still use cursors ?

Here is an example of typical cursor and how to avoid it.

 

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 utilized 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


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

Leave a Reply

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

%d bloggers like this: