Orignal Post: http://www.sql-server-performance.com/articles/per/operations_no_cursors_p2.aspx

But i have modfiy the sql as below:

DECLARE @iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@rowValue nvarchar(255)

– Initialize variables
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(id) FROM #table

– Make sure the table has data.
IF ISNULL(@iNextRowId,0) != 0
BEGIN

– start the main processing loop.
PRINT ‘START’
WHILE @iLoopControl = 1
BEGIN
— This is where you perform your detailed row-by-row
— processing.
— Reset looping variables.

SELECT @iCurrentRowId = id, @rowValue = value FROM #table WHERE id = @iNextRowId
PRINT @rowValue + ‘ (ID: ‘ + CAST(@iCurrentRowId as varchar(1000)) +’, Next Row ID: ‘+ CAST(@iNextRowId as varchar(1000)) +’)’

–SELECT @iNextRowId = NULL
– Get the next iRowId
SELECT @iNextRowId = MIN(id) FROM #table WHERE id > @iCurrentRowId

IF ISNULL(@iNextRowId,0) = 0
BREAK;

END –END WHILE LOOP
END –END Check table has data

Advertisement