sql server while loop...

how to use while loop to fetch record in sql server without using cursor...

Can you explain a little more of what your trying to do?

Create a progressbar to display the recordset population during a long rs.open method call?

I think that using a Cursor is the only way to Fetch records in SQl Server, but maybe the question should be.. do you really need to Fetch records? What are you trying to do?

I thought about the terminology used too as Fetch is a cursor method call.

i have storeprocedure in which
i need to open a table and retrive some records and update some values in it...
using cursor im able to do so...
but in many sites it is mentioned that using cursor will use more system resources...
so is there any other possibilities fetching records without using cursor...

Use an Update query.

Update TableName
Set Field1 = Value, Field2 = Value2...
Where....

As BRUCEVDE said - use an UPDATE query. Almost everything can be done in a "single" set-based logic update...

But if you cannot do it in an UPDATE, another way is to loop through a table and process rows - I do this in STORED PROCEDURES all the time

btw - this should have been posted in the DATABASE DEVELOPMENT section of this forum...

Now assuming that your "table" has an integer primary key and you are going to process them in order from first through last...

Code:

---

Declare @PriKey Int

Set @PriKey=(Select Min(ColumnA) From SomeTable)

While @PriKey is not null
Begin
  .
  .
  ... process your row here...
  .
  .
  Set @PriKey=(Select Min(ColumnA) From SomeTable Where ColumnA>@PriKey)
End

---

This code will loop through rows in a table - one at a time. It will do this without incurring CURSOR-type memory issues...