Click here to Skip to main content
12,360,217 members (62,516 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

983.5K views
26 bookmarked
Posted

How to use Cursor in Sql

, 13 Nov 2011 CPOL
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).

You may also be interested in...

Comments and Discussions

 
GeneralThanks for sharing Pin
Alireza_136227-May-16 9:22
memberAlireza_136227-May-16 9:22 
GeneralMy vote of 5 Pin
manoj kumar choubey12-May-15 19:16
membermanoj kumar choubey12-May-15 19:16 
GeneralMy vote of 5 Pin
Mr Guy12430-Dec-14 6:45
memberMr Guy12430-Dec-14 6:45 
GeneralMy vote of 5 Pin
vanni6326-Aug-14 21:50
membervanni6326-Aug-14 21:50 
QuestionUse 'Fetch next' one time Pin
soujiro seta9-Dec-13 0:15
membersoujiro seta9-Dec-13 0:15 
Questionexcel Pin
Member 15336699-Aug-13 2:29
memberMember 15336699-Aug-13 2:29 
GeneralMy vote of 5 Pin
Member 78440741-Aug-13 21:38
memberMember 78440741-Aug-13 21:38 
GeneralMy vote of 5 Pin
arjun thakur CIS25-Jul-13 1:29
memberarjun thakur CIS25-Jul-13 1:29 
GeneralMy vote of 5 Pin
Jerrykan22-Jan-13 6:08
memberJerrykan22-Jan-13 6:08 
QuestionAbout cursor Pin
Jerrykan21-Jan-13 5:48
memberJerrykan21-Jan-13 5:48 
AnswerRe: About cursor Pin
kanjolia gopal11-Dec-13 1:08
memberkanjolia gopal11-Dec-13 1:08 
QuestionBasics of Cursors - SQL Server Pin
etechpulse8-Jan-13 19:56
memberetechpulse8-Jan-13 19:56 
GeneralMy vote of 3 Pin
Member 95218571-Nov-12 23:55
memberMember 95218571-Nov-12 23:55 
GeneralFirst, your "Table_Training_Detalis" alias in your example d... Pin
KP Lee14-Nov-11 18:52
memberKP Lee14-Nov-11 18:52 
GeneralI haven't seen any evidence of a lack of awareness of the cu... Pin
KP Lee14-Nov-11 18:18
memberKP Lee14-Nov-11 18:18 
Generalit is batter to avoid to use cursor. performance is less, cu... Pin
Dhol Gaurav7-Nov-11 5:39
memberDhol Gaurav7-Nov-11 5:39 
GeneralReason for my vote of 4 good Pin
sandeep08mca7-Nov-11 2:15
membersandeep08mca7-Nov-11 2:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web01 | 2.8.160621.1 | Last Updated 13 Nov 2011
Article Copyright 2011 by kanjolia gopal
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid