Dynamic Cursor in SQL Server 2008

In this article I describe Dynamic cursor.
  • 4156

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:

cursor-in-sql.jpg

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:

open dynamic_cursor

Fetching first data from Dynamic Cursor:

This fetches the first row of specified table:

fetch first from dynamic_cursor

Output:

dynamic-cursor-in-sql-server-first1.jpg

Closing the Dynamic Cursor:

We close Dynamic Cursor as in the following:

close dynamic_cursor

Dealloting Static Cursor:

We deallocate S Cursor as following:

deallocate dynamic_cursor

© 2020 DotNetHeaven. All rights reserved.