dynamic cursor - sorting in declaration

[£ukasz W.]

Hello everybody!

I have a small table "ABC" like this:

id_position | value
---------------------------
1 | 11
2 | 22
3 | 33

I try to use a dynamic cursor as below.
When the statement "order by id_position" in declare part of the cursor_abc
is omitted - cursor work as it should.
But when the statement "order by id_position" is used, cursor behave as
static one.
What's the matter, does anybody know?

Code:

declare @id_position as int, @value as int

DECLARE cursor_abc CURSOR
FOR
select id_position, value from abc
order by id_position

set nocount on
open cursor_abc
FETCH NEXT FROM cursor_abc
INTO @id_position, @value

WHILE @@FETCH_STATUS = 0
BEGIN

print @id_position
print @value
print '----------------------------'

update abc set value=666 --next reading should give value=666

FETCH NEXT FROM cursor_abc
INTO @id_position, @value

END

CLOSE cursor_abc
DEALLOCATE cursor_abc
GO
Regards
Lucas

[Ed Murphy]

£ukasz W. wrote:

I try to use a dynamic cursor as below.

Cursors should be avoided if at all possible.

print @id_position
print @value
print '----------------------------'

Is this just a quick-and-dirty test? If you're trying to generate an
actual production file like this, then you should seriously consider
having the database output raw data, and using some separate tool to
apply formatting.

update abc set value=666 --next reading should give value=666

This is obviously dummy code. What are you actually trying to do
here - apply some function and use the result to control which row is
output next? What does that function look like? It may be possible
to rewrite the whole thing without cursors; failing that, you should
seriously consider having the database output data unsorted, or sorted
in a simple fashion, and using some separate tool (possibly the same
one used to apply formatting) to apply the complex sort rule.

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

In many years of writing SQL, I have seldom found a need for a cursor.
They usually run 1-2 orders of magnitude slwoer than a relational
solution.

When someone uses one, it is generally becasue they are mimicing a
magnetic tape file system, and probably violating the basic principle
of a tiered architecture that display is done in the front end and
never in the back end. This a more basic programming principle than
just SQL and RDBMS.

Finally, id_position is not an ISO-11179 data element name and it makes
no sense. Identifier of what? Position of what? You have two
adjectives without a noun. But I bet you mant it to be PHYSICAL
location because you are mimicing a magnetic tape file system, instead
of using SQL for an RDBMS.

What is your real problem? Show us and perhaps we can help you.