In T-SQL / SQL Server 2000, referencing a particular row of a result set

I want to reference the nth row of the #temptable (at the second SQL comment is below). What expression will allow me to do so?

DECLARE @counter INT
SET @counter = 0
WHILE (@counter<count(#temptable))
--#temptable has one column and 0 or more rows
BEGIN
DECLARE @variab INT
EXEC @variab = get_next_ticket 3906, 'n', 1

INSERT INTO Student_Course_List
                SEL开发者_如何学运维ECT  @student_id,
                -- nth result set row in #temptable, where n is @count+1
                @variab

SET @counter = @counter +1
END

Cursor (will this work?):

for record in (select id from #temptable) loop 
--For statements, use record.id 
end loop;

---

Normally in a relational database like SQL Server, you prefer to do set operations. So it would be best to simply have INSERT INTO tbl SOMECOMPLEXQUERY even with very complex queries. This is far preferable to row processing. In a complex system, using a cursor should be relatively rare.

In your case, it would appear that the get_next_ticket procedure performs some significant logic which is not able to be done in a set-oriented fashion. If you cannot perform it's function in an alternative set-oriented way, then you would use a CURSOR.

You would declare a CURSOR on your set SELECT whatever FROM #temptable, OPEN it, FETCH from the cursor into variables for each column and then use them in the insert.

---

Instead of using a while loop (with a counter like you are doing) to iterate the table you should use a cursor

Syntax would be:

DECLARE @id int
DECLARE c cursor for select id from #temptable
begin
  open c
    fetch next from c into @id
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --Do stuff here
      fetch next from c into @id
    END
  close c
  deallocate c
end