SQL Server cursor Quick Start

There are four basic steps to use a cursor: declare the cursor, open the cursor, extract data, and close the cursor..

As shown in the following SQL example:

 

-- ========================================================== =====
-- Author: <fighting boat>
-- Create Date: <2011-04-19>
-- Description: <SQL Server cursor demo>
-- ========================================================== =====

-- Temporary table
Create   Table # Temptb
(
ID Int   Identity ,
Cola Varchar ( 50 ),
COLB Varchar ( 50 )
)
Declare   @ I   Int = 0 ;
While ( @ I < 10 )
Begin
Insert   Into # Temptb
(Cola, COLB)
Values (
' Test Field cola ' + Cast ( @ I   As   Varchar ( 10 ))
,
' Test Field COLB ' + Cast ( @ I   As   Varchar ( 10 ))
)
Set   @ I = @ I + 1 ;
End
-- Select   *   From # Temptb;

-- Define three variables to receive the cursor traversal values.
Declare   @   Varchar ( 50 ),
@ B   Varchar ( 50 ),
@ C   Varchar ( 50 );

-- Declare the cursor and fill in the data
Declare Cur Cursor   For   Select   *   From # Temptb
-- Open cursor
Open Cur
-- Fill in the next Data Entry
Fetch   Next   From Cur Into   @ , @ B , @ C
Select   @ , @ B , @ C ;
-- @ Fetch_status | 0 indicates that the extraction is successful. 1 indicates that the statement fails or the row is not in the result set. 2. The extracted row does not exist.
While ( @ Fetch_status = 0 )
Begin
-- To do something //
Fetch   Next   From Cur Into   @ , @ B , @ C ;
Select   @ , @ B , @ C ;
End
-- Close cursor
Close Cur
-- Delete a cursor Resource
Deallocate Cur

Drop Table# Temptb;