SQL Cursor Basic Usage

Code highlighting produced byActipro codehighlighter (freeware) http://Www.CodeHighlighter.com/--> 1 table1 structure as followsIdintnamevarchar( -)Declare @id intDeclare @name varchar( -)DeclareCursor1cursor  for         --Defining Cursors Cursor1Select *  fromTable1--objects using cursors (fill in select text as required)OpenCursor1--Open CursorFetch Next  fromCursor1 into @id,@name  --move the cursor down by 1 rows to get the data into the previously defined variable @id, @name while @ @fetch_status=0           --determine if the data was successfully obtainedbeginUpdateTable1SetName=Name+'1'whereId=@id                           --Handle it accordingly (fill in the SQL text as needed)Fetch Next  fromCursor1 into @id,@name  --move the cursor down by 1 rowsEndCloseCursor1--Close CursorsdeallocateCursor1

Cursor General format:
DECLARE cursor name cursor for SELECT field 1, Field 2, Field 3,... From table name WHERE ...
OPEN cursor Name
FETCH NEXT from cursor name into variable name 1, variable name 2, variable name 3,...
While @ @FETCH_STATUS =0
BEGIN
SQL statement Execution process ...
FETCH NEXT from cursor name into variable name 1, variable name 2, variable name 3,...
END
CLOSE cursor Name
DEALLOCATE cursor name (delete cursor)

Code highlighting produced byActipro codehighlighter (freeware) http://Www.CodeHighlighter.com/--> Example:/*function: Database table tbl_users data deptid userid username a 101 B 102 C requires an SQL statement to output the following results DeptID Usern Ame AB c[requires a cursor to implement the design: ok_008 time: 2006-05 Note: no*/Create Table#Temp1 (DeptIDintUseridint, usernamevarchar( -))--data tables to be testedCreate Table#Temp2 (DeptIDint, usernamevarchar( -))--Results Table--Insert some of the data to be tested into the table #temp1 to be tested firstInsert  into#Temp1Select 1, -,'a' Union  AllSelect 1,101,'b' Union  AllSelect 1,131,'D' Union  AllSelect 1,201,'F' Union  AllSelect 2,302,'C' Union  All Select 2,202,'a' Union  AllSelect 2,221,'e' Union  AllSelect 3,102,'y' Union  All Select 3,302,'e' Union  AllSelect 3,121,'T' --Declare @deptid int,@username varchar( -)--Defining CursorsDeclareSelect_cursorcursor  for        SelectDeptid,username from#Temp1OpenSelect_cursorFetch Next  fromSelect_cursor into @deptid,@username    --The column data for the fetch operation is placed in the local variable while @ @fetch_status=0      --returns the state of the last cursor executed by the FETCH statement/*@ @FETCH_STATUS =0 Fetch statement succeeds @ @FETCH_STATUS =-1 FETCH statement fails or the row is not in the result set @ @FETCH_STATUS =-2 fetched rows do not exist*/        begin                  --append @username values directly to the column username when the same data exists for the table #temp2 column DeptID                  if(exists(Select *  from#Temp2whereDeptID=@deptid ))                           Update#Temp2SetUsername=Username+@username whereDeptID=@deptid                  Else                   --inserting new data                          Insert  into#Temp2Select @deptid,@username                  Fetch Next  fromSelect_cursor into @deptid,@username        EndCloseSelect_cursordeallocateSelect_cursorSelect *  from#Temp2--Test ResultsDrop Table#Temp1, #Temp2

SQL Cursor Basic Usage