I wish to reference the nth row from the #temptable (in the second SQL comment is below). What expression will let me achieve this?

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
                SELECT  @student_id,
                -- nth result set row in #temptable, where n is @count+1
                @variab

SET @counter = @counter +1
END

Cursor (will the work?):

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

Normally inside a relational database like SQL Server, you'd rather do set procedures. So it might be better to only have INSERT INTO tbl SOMECOMPLEXQUERY despite very complex queries. This really is far more suitable to row processing. Inside a complex system, utilizing a cursor ought to be relatively rare.

Inside your situation, it appears that the get_next_ticket procedure works some significant logic which can't be completed in a collection-oriented fashion. If you fail to perform it's function within an alternative set-oriented way, you would then make use of a [cde].

You'd declare a CURSOR in your set CURSOR, SELECT whatever FROM #temptable it, [cde] in the cursor into variables for every column after which rely on them within the place.

Rather than utilizing a while loop (having a counter like you do) to iterate the table you need to use a cursor

Syntax could be:

OPEN

Presuming that #TempTable has column Foo:

FETCH

Observe that this can apply a purchase to #TempTable.