SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Friday, November 24, 2006

dynamic cursor - sorting in declaration

SQL Apprentice Question
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



Celko Answers
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 slower 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.

No comments: