Geeks With Blogs
Md. Saiful Islam
| Home |
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
Posted on Tuesday, February 7, 2012 11:28 AM SQL SERVER | Back to top


Comments on this post: An alternative to cursor

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © saiful | Powered by: GeeksWithBlogs.net