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

__________________
The OXYGEN
Delivers edgy, intelligent Technology to all...

create a non-cursor procedure.

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