How to Perform SQL Server Row-by-Row Operations Without Cursors

As you can see, this is a very straight-forward cursor procedure that loops through a table called CustomerTable and retrieves iRowId, vchCustomerNmbr and vchCustomerName for every row. Now we will examine a non-cursor version that does the exact same thing:

if exists (select * from sysobjects where name = N'prcLoopExample')

   drop procedure prcLoopExample

go

CREATE PROCEDURE  prcLoopExample

AS

/*

**  Non-cursor method to cycle through the Customer table and get Customer Info for each iRowId.

**

** Revision History:

** ------------------------------------------------------

**  Date       Name      Description           Project

** ------------------------------------------------------

**  08/12/03   DVDS      Create                -----

**

*/

SET NOCOUNT ON

-- declare all variables!

DECLARE     @iReturnCode       int,

            @iNextRowId        int,

            @iCurrentRowId     int,

            @iLoopControl      int,

            @vchCustomerName   nvarchar(255),

            @vchCustomerNmbr   nvarchar(10)

            @chProductNumber   nchar(30)

-- Initialize variables!

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(iRowId)

FROM   CustomerTable

-- Make sure the table has data.

IF ISNULL(@iNextRowId,0) = 0

   BEGIN

            SELECT 'No data in found in table!'

            RETURN

   END

-- Retrieve the first row

SELECT           @iCurrentRowId   = iRowId,

                 @vchCustomerNmbr = vchCustomerNmbr,

                 @vchCustomerName = vchCustomerName

FROM             CustomerTable

WHERE            iRowId = @iNextRowId

-- start the main processing loop.

WHILE @iLoopControl = 1

   BEGIN

     -- This is where you perform your detailed row-by-row

     -- processing.    

     -- Reset looping variables.           

            SELECT   @iNextRowId = NULL           

            -- get the next iRowId

            SELECT   @iNextRowId = MIN(iRowId)

            FROM     CustomerTable

            WHERE    iRowId > @iCurrentRowId

            -- did we get a valid next row id?

            IF ISNULL(@iNextRowId,0) = 0

               BEGIN

                        BREAK

               END

            -- get the next row.

            SELECT  @iCurrentRowId =   iRowId,

                    @vchCustomerNmbr = vchCustomerNmbr,

                    @vchCustomerName = vchCustomerName

            FROM    CustomerTable

            WHERE   iRowId = @iNextRowId           

   END

RETURN

There are several things to note about the above procedure.

For performance reasons, you will generally want to use a column like "iRowId" as your basis for looping and row retrieval. It should be an auto-incrementing integer data type, along with being the primary key column with a clustered index.

There may be times in which the column containing the primary key and/or clustered index is not the appropriate choice for looping and row retrieval. For example, the primary key and/or clustered index may have already been built on a column using uniqueindentifier as the data type. In such a case, you can usually add an auto-increment integer data column to the table and build a unique index or constraint on it.

The MIN function is used in conjunction with greater than “>” to retrieve the next available iRowId. You could also use the MAX function in conjunction with less than “<” to achieve the same result:

SELECT    @iNextRowId = MAX(iRowId)

FROM      CustomerTable

WHERE     iRowId < @iCurrentRowId

Be sure to reset your looping variable(s) to NULL before retrieving the next @iNextRowId value. This is critical because the SELECT statement used to get the next iRowId will not set the @iNextRowId variable to NULL when it reaches the end of the table. Instead, it will fail to return any new values and @iNextRowId will keep the last valid, non-NULL, value it received, throwing your procedure into an endless loop.  This brings us to the next point, exiting the loop.

When @iNextRowId is NULL, meaning the loop has reached the end of the table, you can use the BREAK command to exit the WHILE loop.  There are other ways of exiting from a WHILE loop, but the BREAK command is sufficient for this example.

You will notice that in both procedures I have included the comments listed below in order to illustrate the area in which you would perform your detailed, row-level processing.

-- This is where you perform your detailed row-by-row

-- processing.    

Quite obviously, your row level processing will vary greatly, depending upon what you need to accomplish. This variance will have the most profound impact on performance.

For example, suppose you have a more complex task which requires a nested loop. This is equivalent to using nested cursors; the inner cursor, being dependent upon values retrieved from the outer one, is declared, opened, closed and deallocated for every row in the outer cursor. (Please reference the DECLARE CURSOR section in SQL Server Books Online for an example of this.)  In such a case, you will achieve much better performance by using the non-cursor looping method because SQL is not burdened by the cursor activity.