SQL SP without Cursors for Row by Row Operations

Hi...

As everyone now's we can use Cursors for row-by-row operations in SQL Stored procedures. The drawback of using this option is Performance. So to overcome the issue we can write a work around code to implement row-by-row operations in your SQL stored procedure. Following are the code written for this with appropriate comments to explain its operation.

BEGIN

-- Here we Initialize variables

DECLARE @iNextRowId int,@iCurrentRowId int,@iLoopControl int,@FundID int, @FundIndexId int

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(ID)FROM Fund_Index_Relationship Where Fund_ID = @ID

-- Retrieve 1st row

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId AND Fund_ID = @ID,

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- Loop is started

WHILE @iLoopControl = 1

BEGIN

SELECT @iNextRowId = NULL

-- Getting next ID

SELECT @iNextRowId = MIN(ID)

FROM Fund_Index_Relationship

WHERE Fund_ID = @ID AND ID > @iCurrentRowId

-- check whether there is a ID next

IF ISNULL(@iNextRowId,0) = 0

BEGIN

BREAK

END

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- get the next row.

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId

END

END