Alternative for SQL Cursor?[Solved]

I think it depends on what you want to do. I use while loops and user defined functions

Your question is a little confusing because you are talking about a cursor and then about VB6. I guess you are opening a cursor on SQL Server from VB6. I generally don't like this approach (as you said) - but especially if you are connecting your cursor to a non temporary table.

If you have the chance to redesign the database and its interactions, then I would strongly suggest moving as much logic into stored procedures as possible and so avoid cursors in VB6 that way.

If you do not have this luxury, then I would suggest you use cursors or multiple insert statements to load up a temporary table and then use TSQL to use the temporary table to insert/update the permanent one. The idea is to get all long running activities off permanent tables by either converting them into lots of little actions or making them happen faster.

The best performance I have seen using SQL Server and VB6 is via using disconnected record sets for reads (the whole record set is loaded into the client and then when you loop over it you are not using a cursor). Then separate multiple inserts are used to communicate the other way. This might seem the counter intuitive, but it actually gives the RDBMS the best chance to optimize its self.

The best alternative to Cursor is a Select statement.

Cursor actually creates a temporary table to loop through the data. So if you are creating a temporary table yourself, then it is no good for you.

So manage the datamanipulation directly from the SQL.
:thumbsup: