Static Cursor in SQL Server 2008

In this article I describe Static cursor in SQL server 2008.
  • 2754

Introduction

In this article I describe Static cursor. With the help of Static Cursor we can find  first, next, last and any specific 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

Static Cursor:

 A static cursor displays the result set as it was at the time of opening cursor. Through static cursor you cant update or delete the data. If we open static cursor, and after that we make changes in original data source i.e. suppose we insert a row in table, then the cursor will not reflect any changes made in table i.e. inserted rows will not be displayed.

Syntax:

Declare cursor_name cursor
static
for
sql statement

where,

cursor_name- is the name of the cursor.

static: is keyword which denotes cursor as static.

sql statement: is the sql statement that you want to execute using cursor.

Example:

Declaration of Static cursor:

We declare a Static Cursor as in the following:

declare static_cursor cursor
static for
select * from emp

Opening of static cursor:

We open a Static Cursor as in the in following:

open static_cursor

Fetching first data from Static Cursor:

fetch first from static_cursor

Output:

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

Closing the Static Cursor:

We close Static Cursor as in the following

close static_cursor

Dealloting Static Cursor:

We deallocate Static Cursor as following

deallocate static_cursor

© 2020 DotNetHeaven. All rights reserved.