Tuesday, February 24, 2009

Example for converting Cursor to While loop

Just a small example on how to replace a cursor using an while loop.

Table t1 has one column id of type int.

Assume that a cursor is defined for the query:

Select id from t1

This example narrates how a cursor defined for the above mentioned query
can be replaced using While loop and table variable.

DECLARE  @tbl_var  TABLE(
                         tid     INT
                         row_num INT
    IDENTITY ( 1 , 1 )

DECLARE  @rowcnt INT


DECLARE  @row_move INT

INSERT INTO @tbl_var
FROM   t1

SET @rowcnt = @@ROWCOUNT

SET @row_move = 1

WHILE @rowcnt >= @row_move
    SELECT @val = tid
    FROM   @tbl_var
    WHERE  row_num = @row_move
    EXEC Sample_sp_call

    SET @row_move = @row_move + 1

No comments: