T-SQL usage 3 (cursor and fetch)

/In general, when we use the select statements, they are all for a row of records,
If you want to read a multi-row record (that is, a record set) in the query analyzer, you need to use a cursor or a while loop.
/
The following is an excerpt from
Http://www.cnblogs.com/aotian56/archive/2007/07/13/817281.html
/
Cursor type:
1. Static cursor (does not detect changes in data rows)
2. Dynamic Cursor (reflecting changes to all data rows)
3. Forward cursor only (scroll is not supported)
4. Key set cursor (can reflect modifications, but cannot accurately reflect insertion or deletion)

Order of cursor usage:
1. Define a cursor
2. Open the cursor
3. Use a cursor
4. Close the cursor
5. Release cursor

Transact-SQL:
Declare cursor name cursor [local | Global] [forward_only | scroll] [static | keyset | dynamic] [read_only | scroll_locks]
For selet statement [for update [of column name [, column name]
Note: Local local cursor global Global cursor
Forward_only only scroll forward scroll
Static keyset
Read_only read-only scroll_locks lock the current row of the cursor

Obtain cursor data
Fetch [[next | prior | first | last |
Absolute {n | @ nvar | relative {n | @ nvar}]
From] cursor name [into variable]
Note:
Next next line prior last line first line
Last last line absolute N
Line n starting from the current position of relative n
The into variable assigns values of fields in the current row to the variable.

Cursor status variable:
@ Fetch_status cursor status
0 success-1 failure-2 loss
@ Cursor_rows the number of rows in the result set in the cursor
N rows-1 cursor is a dynamic 0 empty set cursor
The current row of the Operation cursor:
Current of cursor name

In the following example, the test is successful on SQL Server 2000.

Use Pubs
Go

Declare   @ Auid   Char ( 12 ), @ Aulname   Varchar ( 20 ), @ Aufname   Varchar ( 20 ), @ St   Char ( 2 ), @ Auinfo   Varchar ( 50 )
Declare Auth_cur Cursor   For
Select Au_id, au_lname, au_fname, state
From Authors

OpenAuth_cur

Fetch   Next   From Auth_cur Into   @ Auid , @ Aulname , @ Aufname , @ St
While ( @ Fetch_status = 0 )
Begin
Print   ' Author No: ' + @ Auid
Print   ' Author name: ' + @ Aulname + ' , ' + @ Aufname
Print   ' Region: ' + @ St
Print   ' -------------------------- '
Fetch   Next   From Auth_cur Into   @ Auid , @ Aulname , @ Aufname , @ St
End

CloseAuth_cur
DeallocateAuth_cur

Transact-SQL cursors are mainly used in Stored Procedures, triggers, and transact-SQL scripts. They allow the content of the result set to be used in other Transact-SQL statements.
/* Another example
From: http://www.cnblogs.com/BillChen/archive/2008/08/21/690180.html
The original example is used to explain how to use while to replace the cursor and its advantages. Here, we only extract the example of using the cursor.
*/
Declare   @ Tbtargetpermissions Table (Targetpermissionid Uniqueidentifier   Not   Null   Primary   Key )
Insert   Into   @ Tbtargetpermissions  
Select   [ Targetpermissionid ]  
From   [ Ps_rolepermissions ]  
Where   [ Targetid ]   =   @ Targetid   And   [ Roleid ]   =   @ Roleid

Declare   @ Targetpermissionid   Uniqueidentifier ;

-- Define a cursor
Declare Targetpermissions_byroleid_cursor Cursor   For  
Select   [ Targetpermissionid ]   From   @ Tbtargetpermissions  

-- Open cursor
Open Targetpermissions_byroleid_cursor

-- Read the first record of the cursor
Fetch   Next   From Targetpermissions_byroleid_cursor Into   @ Targetpermissionid

-- Check the value of @ fetch_status for cyclic reading.
While   @ Fetch_status   =   0
Begin
Exec Ps_targetpermissionentity_select @ Targetpermissionid ;

Fetch   Next   From Targetpermissions_byroleid_cursor Into   @ Targetpermissionid ;
End

-- Close cursor
Close Targetpermissions_byroleid_cursor
Deallocate Targetpermissions_byroleid_cursor