An alternative to cursor

While working with database we often require to use Cursor. Cursors force the database engine to repeatedly fetch rows. This may slow down your database and locking may occur. As a result cursor are not suggested to use if not extremely necessary.

If there is a primary key on a table, you can usually write a WHILE loop to do the same work without incurring the overhead of a cursor. The following example demonstrate the idea:

Declare @ItemId bigint

Declare @OldItemCode varchar(50)

      SELECT Top 1   @ItemId= ItemId, @OldItemCode=OldItemCode

      FROM         InvItem

      WHERE      (IsStockItem = 'true')

      WHILE @ItemId is not null

      BEGIN

            print 'Item: '+convert(varchar(10),@ItemId)+' - '+convert(varchar(50),@OldItemCode)

            if exists (select ItemId from InvItem where IsStockItem=1 and ItemId>@ItemId)

                  begin

                        SELECT Top 1    @ItemId=ItemId, @OldItemCode=OldItemCode

                        FROM         InvItem

                        WHERE      (IsStockItem = 'true') and ItemId>@ItemId

                  end

                  else

                  begin

                        set @ItemId=null

                  end

      END