Today one of my colleagues named sukanya Gopalakrishnan (junior s/w Engineer) came and ask me some doubt about cursor. So I thought to write this topic which will help the fresher’s and also some experienced guys. Here I am explaining a simple cursor.
- What is a cursor?
Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.
- Let us look this example.
Here I am just taking 2 columns from a table and passing through the cursor and printing them.
CREATE PROCEDURE Usp_cursor_test
AS
BEGIN
–Declaring the variables needed for cursor to store data
DECLARE @Name VARCHAR(50)
DECLARE @EmptypeID INT
–Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table
DECLARE cur_print CURSOR FOR
SELECT name,
emptypeid
FROM employee.employeedetails
–After declaring we have to open the cursor
OPEN cur_print
–retreives the First row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
— @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against
— any cursor currently opened by the connection.
— @@FETCH_STATUS = 0 means The FETCH statement was successful.
— @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set.
— @@FETCH_STATUS = -2 The row fetched is missing.
WHILE @@FETCH_STATUS = 0
BEGIN
–Operations need to be done,Here just printing the variables
PRINT @Name
PRINT @EmptypeID
–retreives the NExt row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
END
–Closing the cursor
CLOSE cur_print
— removes the cursor reference and relase cursor from memory
— very Important
DEALLOCATE cur_print
END
Note:- Once cursor is opened we have to close the cursor
- After the usage cursor should be deallocated from the memory.
- As a DBA , I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.
Preethy Kamath
14 years agoHai varun,
Can u please include some DB performance tuning tips and its application scenario in next posts.
rathish
14 years agosuper keep it up
velu.sp
14 years agocan you explain about implicit and explicit cursor with example?
Varun
14 years agoHi Velu,
i hope you got this question from an intervew. In sql server , if the cursor is automatically deallocated it is called Implicit cursor, if it is deallocated using the command dealocate cursor @cur , then it is explicit cursor.
In the Oracle world a explicit cursor is a cursor you define in the declare section while an implicit cursor is what Oracle does for you each time you do not define an explicit one.
shahthini
14 years agoplzzzzzzz help how to execute this
Varun R
14 years agoyou can execute this by execute option in sql server or by F5…
shiny
14 years agoIts was really useful n easy to follow u…..
Bipin Patel
14 years agoThis is one the best example of the cursor, but also give some extra creativity example for cursor…
suraj kumar
14 years agosuper…..
santhosh
14 years agoHI VARUN,it’s really good n if possible can u post some imp interview questions on sql server for 2+ exp…Plz or send to my mail id(santhosh.niceguy@gmail.com).
Dhiru Rajpoot
14 years agonice Article sir….this is very help full for fresher
Priyanka
14 years agoi would like to know how u get data if ID’s are there in present table and we have to get the data from other table when we do multiple selection??/
Varun R
14 years agoCould you please explain the scenario bit more ?
kkcmania
14 years agoGood example to start with for the newbies.
Pradeep
14 years agoHi Varun,
Thanks for cursor example, in additon to the above example can you alter the same logic using CASE Statement.
Thanks in advance,
Pradeep
sandeep pandey
14 years agovery good artical for beginners
thanku so much
Yugandhar
14 years agoHi Varun.! This is very good article about cursors. It’s helped me alot.
And also i want know about SQL Profiler, so could you please write the article about this.???
Yugandhar
surender
14 years agoThanks for cursor example, in additon to the above example can you alter the same logic using CASE Statement.
sss
14 years agonice
Tara
14 years agoHi, I do thin thios is an excellnt site. I stumbledupon it 😉 I’m going to come back yet again since i have bookmarked it.
Money and freedom is the best way to change,
may you be rich and continue to guide other people.
sinthu
14 years agoits very UseFul
sinthu
14 years agoCould You Explain Triggers With Examples For Beginners?…
Remya
14 years agoVarun,
Can u pls tell me actually where i can use this Cursor in Sql Server even it have performance issues?
Murugan.vc
14 years agonice
saurabh
14 years agohello varun
can u explain how to add cursor in any sql jobs..plz..
Thank you
Varun R
14 years agoCan u refine this question?
Lona
14 years agoDoes your site have a contact page? I’m having trouble locating it but, I’d like
to shoot you an e-mail. I’ve got some creative ideas for your blog you might be interested in hearing.
Either way, great blog and I look forward to seeing it expand over time.
shiva
14 years agogood post !!!