articles >> performance tuning >> How to Perform SQL Server Row-by-Row Operations ...

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

By : David VanDeSompele Nov 17, 2004

Page 3 / 3

Here is an example procedure with a nested loop and no cursors:

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

   drop procedure prcNestedLoopExample

go

CREATE PROCEDURE  prcNestedLoopExample

AS

/*

**  Non-cursor method to cycle through the Customer table **  and get Customer Name for each iCustId. Get all
**  products for each iCustid.

**

** Revision History:

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

**  Date        Name       Description       Project

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

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

**

*/

 

SET NOCOUNT ON

-- declare all variables!

DECLARE     @iReturnCode          int,

            @iNextCustRowId       int,

            @iCurrentCustRowId    int,

            @iCustLoopControl     int,

            @iNextProdRowId       int,

            @iCurrentProdRowId    int,

            @vchCustomerName      nvarchar(255),

            @chProductNumber      nchar(30),

            @vchProductName       nvarchar(255)

           

-- Initialize variables!

SELECT     @iCustLoopControl = 1

SELECT     @iNextCustRowId = MIN(iCustId)

FROM  Customer

 

-- Make sure the table has data.

IF ISNULL(@iNextCustRowId,0) = 0

   BEGIN

            SELECT 'No data in found in table!'

            RETURN

   END

 

-- Retrieve the first row

SELECT      @iCurrentCustRowId = iCustId,

            @vchCustomerName = vchCustomerName

FROM  Customer

WHERE       iCustId = @iNextCustRowId

 

-- Start the main processing loop.

WHILE @iCustLoopControl = 1

   BEGIN

        -- Begin the nested(inner) loop.

        -- Get the first product id for current customer.

            SELECT  @iNextProdRowId = MIN(iProductId)

            FROM    CustomerProduct

            WHERE   iCustId = @iCurrentCustRowId

           

         -- Make sure the product table has data for
         -- current customer.

         IF ISNULL(@iNextProdRowId,0) = 0

            BEGIN

              SELECT 'No products found for this customer.'

            END

         ELSE

            BEGIN

           -- retrieve the first full product row for
           -- current customer.

              SELECT  @iCurrentProdRowId = iProductId,

                       @chProductNumber = chProductNumber,

                       @vchProductName = vchProductName

              FROM  CustomerProduct

              WHERE           iProductId = @iNextProdRowId

             END

 

            WHILE ISNULL(@iNextProdRowId,0) <> 0

           BEGIN

        -- Do the inner loop row-level processing here.

        -- Reset the product next row id.

                SELECT  @iNextProdRowId = NULL

                                   

       -- Get the next Product id for the current customer

                SELECT @iNextProdRowId = MIN(iProductId)

                FROM  CustomerProduct

                WHERE iCustId = @iCurrentCustRowId

                AND  iProductId > @iCurrentProdRowId

 

     -- Get the next full product row for current customer.

             SELECT     @iCurrentProdRowId = iProductId,

                        @chProductNumber = chProductNumber,

                        @vchProductName = vchProductName

             FROM  CustomerProduct

             WHERE           iProductId = @iNextProdRowId

           END

 

      -- Reset inner loop variables.

            SELECT       @chProductNumber = NULL

            SELECT       @vchProductName = NULL

            SELECT       @iCurrentProdRowId = NULL

 

       -- Reset outer looping variables.  

            SELECT           @iNextCustRowId = NULL

           

       -- Get the next iRowId.

            SELECT           @iNextCustRowId = MIN(iCustId)

            FROM  Customer

            WHERE           iCustId > @iCurrentCustRowId

 

       -- Did we get a valid next row id?

            IF ISNULL(@iNextCustRowId,0) = 0

               BEGIN

                        BREAK

               END

 

        -- Get the next row.

            SELECT    @iCurrentCustRowId = iCustId,

                      @vchCustomerName = vchCustomerName

            FROM  Customer

            WHERE     iCustId = @iNextCustRowId

           

   END

 

RETURN

In the above example we are looping through a customer table and, for each customer id, we are then looping through a customer product table, retrieving all existing product records for that customer. Notice that a different technique is used to exit from the inner loop. Instead of using a BREAK statement, the WHILE loop depends directly on the value of @iNextProdRowId. When it becomes NULL, having no value, the WHILE loop ends.

Conclusion

SQL Cursors are very useful and powerful because they offer a high degree of row-level data manipulation, but this power comes at a price: negative performance. In this article I have demonstrated an alternative that offers much of the cursor’s flexibility, but without the negative impact to performance. I have used this alternative looping method several times in my professional career to the benefit of cutting many hours of processing time on production SQL Servers.