Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to use Cursor in Sql

, 13 Nov 2011
Rate this:
Please Sign up or sign in to vote.
A cursor is a set of rows together with a pointer that identifies a current row.
 
In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, it's like recordset in ASP and Visual Basic.
 
Typical syntax of cursor is:
 
Syntax
DECLARE @fName varchar(50), @lName varchar(50)
 
DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC
 
FOR
 
Select firstName, lastName FROM myTable
 
OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName
 
   INTO @fName, @lName
 
   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0
 
BEGIN
 
   FETCH NEXT FROM cursorName
 
   INTO @fName, @lName
 
   PRINT @fName + ' ' + @lName -- print the name

END
 
CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor
Example
DECLARE @ColExpir datetime
DECLARE @ColFallprotec datetime
DECLARE @ColWorkid int
--------------------------------------------------------
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
FROM   Table_Courses 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
 
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
Reference

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

kanjolia gopal
Software Developer (Senior)
India India
Working as Sr.Software Engineer for last 5 years.
Experienced IT professional in VB,C#,ASP.net,MVC,WCF.
 
Microsoft Certified Professional Developer (MCPD Certified).
Follow on   Twitter

Comments and Discussions

 
QuestionUse 'Fetch next' one time Pinmembersoujiro seta9-Dec-13 0:15 
Questionexcel PinmemberMember 15336699-Aug-13 2:29 
GeneralMy vote of 5 PinmemberMember 78440741-Aug-13 21:38 
GeneralMy vote of 5 Pinmemberarjun thakur CIS25-Jul-13 1:29 
GeneralMy vote of 5 PinmemberJerrykan22-Jan-13 6:08 
QuestionAbout cursor PinmemberJerrykan21-Jan-13 5:48 
AnswerRe: About cursor Pinmemberkanjolia gopal11-Dec-13 1:08 
QuestionBasics of Cursors - SQL Server Pinmemberetechpulse8-Jan-13 19:56 
GeneralMy vote of 3 PinmemberMember 95218571-Nov-12 23:55 
GeneralFirst, your "Table_Training_Detalis" alias in your example d... PinmemberKP Lee14-Nov-11 18:52 
GeneralI haven't seen any evidence of a lack of awareness of the cu... PinmemberKP Lee14-Nov-11 18:18 
Generalit is batter to avoid to use cursor. performance is less, cu... PinmemberDhol Gaurav7-Nov-11 5:39 
GeneralReason for my vote of 4 good Pinmembersandeep08mca7-Nov-11 2:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 13 Nov 2011
Article Copyright 2011 by kanjolia gopal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid