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.