Cursors in SQL Server
What is CURSOR ?
A SQL cursor is a database object which is used to retrieve data from a result set one row at a time.
A SQL cursor is used when the data needs to be updated row by row.
Why to use Cursors ?
In databases, operations are made on set of rows.
For example, a SELECT statement returns a set of rows which is called a result set and also sometimes the application logic needs to work with one row at a time rather than the entire result set at once.
This can be done using cursors.
Cursor Example :
In this example we will fill the blank data with previous values from a table using a cursor
Here is a table as EmployeeDetails1 where we have two complete data and two empty data in Name column as shown below
Now with the help of cursor, we will fill this blank data with previous given value (i.e. on ID=2 the data on Name Column will come as “abc” and on Id=4 the data will come as “pqr”.
Steps to create and use a Cursor.
1.Declaring Cursor :
A cursor is declared for defining SQL statements.
We have declared a cursor as EmployeeCursor for retrieving ID and Name from table EmployeeDetails1 as shown in above image.
2. Opening a Cursor :
A cursor is opened for storing data which are retrieved from result set.
3. Fetching Cursor :
When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
The FETCH_STATUS value is 0 until there are rows.when all rows are fetched then FETCH_STATUS becomes 1.
Convert the @Id column from int to varchar as shown above.
Then we have used UPDATE statement to fill up the blank data with the previous given values.
4. Closing Cursor :
The cursor should be closed explicitly after data manipulation.
5. Deallocating Cursor :
Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.
Query :
declare @Id int
declare @Name nvarchar(50)
declare @Id1 int
declare @Name1 nvarchar(50)
declare EmployeeCursor cursor for
select * from EmployeeDetails1
open EmployeeCursor
fetch next from EmployeeCursor into @Id, @Name
while (@@FETCH_STATUS = 0)
begin
print ‘Id = ’ + CAST(@Id as varchar(10)) + ‘ Name = ’ + @Name
fetch next from EmployeeCursor into @Id1, @Name1
update EmployeeDetails1
set Name = @Name where id = @Id1
fetch next from EmployeeCursor into @Id, @Name
END
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
Output :
Now execute select * from EmployeeDetails1 and see the results
Interesting below is the video covering on SQL Server basics: -
