Dynamic Cursor in SQL Server 2008
In this article I describe Dynamic cursor.
Introduction
In this article I describe Dynamic cursor. With the help of Dynamic Cursor we can find first, next, last and any relative row of the table.
First we create a table named emp to apply a cursor on this table:
Creation of table
create table emp(emp_id int,em_name varchar(10))
go
insert into emp
select 1,'d' union all
select 2,'e' union all
select 3,'f' union all
select 4,'mahi' union all
select 5,'gill' union all
select 6,'singh'
go
select * from emp
|
Output:
Dynamic Cursor:
A dynamic cursor is opposite to static cursor. If we open dynamic cursor, and after that we make changes in original data source i.e. suppose we delete a row in table, then the cursor will reflect all changes made in table i.e. we can not access deleted row. Dynamic cursor is scrollable and sensitive to database changes. Dynamic cursor is sensitive to any changes in original data source. It supports update ,delete operations.
Syntax:
Declare cursor_name cursor
Dynamic
for
sql statement
|
where,
cursor_name- is the name of the cursor.
Dynamic: is keyword which denotes cursor as dynamic.
sql statement: is the sql statement that you want to use in cursor.
Example:
Declaration of Dynamic cursor:
We declare a Dynamic Cursor as in the following:
declare dynamic_cursor cursor
dynamic for
select * from emp
|
Opening of dynamic cursor:
We open a Dynamic Cursor as in the in following:
Fetching first data from Dynamic Cursor:
This fetches the first row of specified table:
fetch first from dynamic_cursor
|
Output:
Closing the Dynamic Cursor:
We close Dynamic Cursor as in the following:
Dealloting Static Cursor:
We deallocate S Cursor as following:
deallocate dynamic_cursor
|