Addressing cursors using a field.(SQL Server)

Question

I have a table that I process using a cursor. Lets say it's structure is like this: RID | School | Order | Text

Now, I filter out the other schools(so only mine is shown) and then I ORDER BY order, so that I get the text arranged how I want. Now, my problem is, the order isn't straight incrementing(though all of them are unique per school) it can skip from going 1,2,3,200,823 etc. but I need to be able to basically do a

SELECT * FROM SCHOOL_DATA WHERE ORDER=@order

with order being the original order value. With cursors(even using ABSOLUTE) it flattens the table so that when an ORDER field skips out of place(from 1 to 2 to 100) it thinks that ORDER=100 is the 3rd row in it's internal table, as it should.

But is it possible to address such a thing with cursors so that I can do

FETCH ABSOLUTE 100..

and actually get the row corresponding to ORDER=100?

Answer 1

In order to be able do that, you'd have to create a query that generates empty rows for all the missing Order values. Seems like a silly thing to do.

I get the impression that cursors are not the right thing for whatever you're trying to achieve, maybe you could explain a little more about the intent, so people can provide viable alternatives?

For example, putting your results in a table variable, and querying that table for the right Order value seems like an alternative.

DECLARE @school int = 1

DECLARE @schoolData TABLE
(
    RID int,
    School int,
    [Order] int,
    Text ntext
);

INSERT INTO @schoolData 
    SELECT * 
    FROM SCHOOL_DATA
    WHERE School=@school ORDER BY [Order]

SELECT * FROM @schoolData WHERE [Order]=100

However. I get the distinct impression that you should be using neither cursors, nor any temporary storage.

Answer 2

It sounds to me like you'd be better of not using a cursor and simply select a row at a time. Based on comments left to Thorarin's answer. Something like:

declare ... all the variables to hold the different columns.
declare @CurrentOrder ... proper datatype

set @CurrentOrder = ... initial value
while(1=1)
begin
  Select @Var1 = Column1, @Var2 = Column2, etc, @GotoWhere = GotoWhere
  from SCHOOL_DATA
  where Order = @CurrentOrder
  select @RC = @@Rowcount, @E = @@ERROR
  ... do error handling if @RC <> 1 or @E <> 0. Can wrap with try catch if using 2005+
  ... do work.
  ... error out if @GotoWhere is null
  if (... done condition ...)
     exit
  set @CurrentOrder = @GotoWhere
end