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.
]]>