Declare @MyCount int
set @MyCount = 0

Select Row_Number() over (order by col1) as RowID,a.*
into #MyTmpData
from MyTable a

while exists (Select * from #MyTmpData a where a.RowID > @MyCount)
BEGIN
  SET @MyCount = @MyCount + 1
  Select .... where RowID = @MyCount
END

DECLARE @MyKey int -- or char or whatever
SET ROWCOUNT 1
SET @MyKey = 0

SELECT @MyKey = MyKey, ...
    FROM MyTable
    WHERE MyKey > @MyKey
WHILE (@@ROWCOUNT != 0)
    BEGIN
    -- processing goes here
    -- set it up for the next iter
    SELECT @MyKey = MyKey, ...
        FROM MyTable
        WHERE MyKey > @MyKey
    END
SET ROWCOUNT 0

DECLARE @MyKey CHAR(12)
SET ROWCOUNT 1
SET @MyKey = 0

WHILE (1=1)
    BEGIN
    SELECT @MyKey = MyKey, ...
        FROM MyTable
        WHERE MyKey > @MyKey
    IF @@ROWCOUNT = 0
        BREAK
    -- processing goes here
    END
SET ROWCOUNT 0

quote:

---

Originally posted by IncisiveOne

2. But if you must revert to 1960's style, then a while loop is more efficient than a cursor. Try it for yourself on a few thousand rows in production; they use different locks and more overhead. Additionally, there are issues with the underlying data (the actual data rows in the table) changing whilst the cursor is active. All tha can be easily avoided with a simple while loop (which remains stupid and slow compared to set-oriented processing).

---

quote:

---

There may be cases where a while loop with selects is more efficient, but your statement is way too general to accept as a general statement.

---

quote:

---

Originally posted by IncisiveOne

quote:

---

There may be cases where a while loop with selects is more efficient, but your statement is way too general to accept as a general statement.

---

Fine. Take the example here. A cursor to do:
SELECT @MyKey = MyKey, ...
FROM MyTable
WHERE MyKey > @MyKey
is more expensive than the select. Period.

Anything worth doing, is worth doing right.

---

quote:

---

It would be helpful if you posted code that would actually work.

---

quote:

---

Your select would probably only go through the loop one time, because it would scan the table in key sequence, and end up with the values from the last row.

---

quote:

---

Originally posted by IncisiveOne

You keep bringing up new "points" without acknowledging that you have lost the previous one.

What is it now, are you suggesting that, given a Relational database, which has a relational logical key in the table, when one has to loop through the rows (which remains stupid and slow in comparison with set-oriented processing), one should ignore the existing order ? Make an additional temp table with a row_id or Identity column instead, just for the query, every execution (with all the additional resources and overhead) ?

Have you noticed that, as suggested in post 4, repeating (select *...) in every execution of the while loop, in addition to the processing inside the loop, is massively inefficient compared to a while loop that is absent such a (select *) ? Have you noticed that post 5 is put forward as a more efficient method ?

quote:

---

It would be helpful if you posted code that would actually work.

---

No doubt. The code "actually works" in production for many years. Why is it that you postulate without actually trying the code, or understanding it ? In post 5, the first is instantly recognisable to people who use cursors, as per OP's question; the second is a tighter construct.

quote:

---

Your select would probably only go through the loop one time, because it would scan the table in key sequence, and end up with the values from the last row.

---

Probably ? Read the manual on set rowcount before speculating about what you do not know. The context is OP's requirement to replace a cursor with a while loop; so no, I am not providing something other than that; and no, this is not the first time; and no, I am not posting untried code (perhaps that is what you are used to here).

I will not waste time arguing with such postulations and speculations. I will answer anyone who has actually bothered to try it.

Anything worth doing, is worth doing right.

---

quote:

---

Maybe you are not aware that SQL Server makes no promises about the order that rows are returned if you do not have an ORDER BY clause.

---

quote:

---

Originally posted by IncisiveOne Feel free to add an ORDER BY where it is necessary, and not where it is not (do read up on clustered index).

---

quote:

---

Originally posted by IncisiveOne

quote:

---

Maybe you are not aware that SQL Server makes no promises about the order that rows are returned if you do not have an ORDER BY clause.

---

Marvellous. Since you understand the issue, and since you are a coder, and since the technique has to do with walking down a sequence of keys, you should know that. Much ado about nothing. Feel free to add an ORDER BY where it is necessary, and not where it is not (do read up on clustered index). Feel free to write nested loops for compound keys and a single loop for single columns keys and IDENTITY columns. Feel free to read and understand the code in the context of the original post, or post nonsense about something else. Hey, it's the SQL Team site !

"Dangerous". Sorry, I did not realise it needed to be said: Any code is dangerous when you do not know the table/indices or what the particular code segemnt does or you haven't a clue about programming or you code for single-user and port the code to production or you think normalisation is dumbing down the Einsteins of the world so that duh Homer Simpsons can understanduh it. But you are welcome to follow me around, and post all the warnings following my posts, to ensure that the Homers do not trip over themselves. I am new to the site, you will have a much better idea re the technical ability of posters, and thus the full extent of warnings and gotchas required. You should check out some of the other threads here, some a very dangerous.

My warning: the code is only as valid as the coder. MVJ: noting the entire thread, and how long it took you to figure out each of the bits, this code is strictly off-limits for you, do not use it under any circumstances. I am not saying you are dishonest and counter-productive, no, I am saying that it is dangerous for you to use this code. This code is only useful (safe) for people who have prior experience with the techniques, who are capable of using a new method, and who can understand it.

TOP 1. That uses a worktable. But I suppose anything is better than conceding IncisiveOne a point. Maybe you can find a way of using a materialised view plus the original cursor that OP wants to replace. Or maybe a select * in every ieration plus a fabricated row_id. Winnie has an additional temp table, maybe you can find a way to use two temp tables. How about an outer join.


Anything worth doing, is worth doing right.
But if you are MVJ, it's worth doing it wrong a few times first.

---

declare @MyKey int
select @MyKey = -9999999

while (1=1)
	begin

	select	top 1
		@MyKey = MyKey
	from
		MyTable
        where
		MyKey > @MyKey
	order by
		MyKey

	if @@rowcount = 0
		break
	
	end

quote:

---

Originally posted by vmessy

Hi all,

Im trying to write a query which will output a row at a time.

---

quote:

---

Originally posted by quibbler

It's not my intent to tar all responders with one brush, but on these forums I see an astonishing level of snobbery, condescension, and insults--in rare cases by people who purportedly work for Microsoft. If you want to make the point that set-based solutions are better than cursor solutions (or any other point), why not just state it plainly? Playing some kind of ego game with newbies doesn't help anyone.

---