Usage example of the midstream mark in SQL Server 2000 [ZT]

The following is the most direct way to use a cursor:
Code Running Condition: A tb_paperinfo (paperid, papername) Table

-- Declare a cursor marked as C. The cursor identifies two columns from the tb_paperinfo table.
Declare C Cursor   For   Select   Top   3 Paperid, papername From Tb_paperinfo

-- Open cursor C
Open C

-- Declare two variables to temporarily store the values of corresponding fields
Declare   @ Paperid   Varchar ( 20 );
Declare   @ Papername   Varchar ( 20 );

-- @ Fetch_status the position of the returned cursor to identify whether the cursor has reached the end or whether it is still on or off
-- When fetch next from C is not executed, @ fetch_status is-1
Fetch   Next   From C Into   @ Paperid , @ Papername -- -- Note: At this time, the number of variables after into corresponds to the number of columns in the SELECT statement. After this statement is executed, @ fetch_status returns 0.


Declare   @ I   Int ;
Set   @ I = 1 ;
While   @ Fetch_status = 0
Begin
/**/ /*Here, you can use the @ paperid and @ papername variables to do what you want.*/
Print   ' The ' +   Cast ( @ I   As Varchar ( 20 )) + ' Paperid of a record: '   + @ Paperid
Print   ' The ' +   Cast ( @ I   As Varchar ( 20 )) + ' Papername of a record: '   + @ Papername

-- Use the fetch next from statement to retrieve the next record.
Fetch   Next   From C Into   @ Paperid , @ Papername
Set   @ I = @ I + 1 ;
End

-- Close the cursor. If you want to use it again, you can use the open method to open it again (open before release)
Close C

-- Release cursor
Deallocate C