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  @val INT


DECLARE  @row_move INT


INSERT INTO @tbl_var
           (tid)
SELECT id
FROM   t1

SET @rowcnt = @@ROWCOUNT


SET @row_move = 1

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

      @val
    
    SET @row_move = @row_move + 1
  END

No comments: