How to Close and Delete Cursor in SQL

In this article I am going to explain how to close and delete a cursor.
  • 10378

Introduction

A cursor is a database object that points to a result set. We use cursor to fetch any specific row from result set. Most of time cursor is used by application programmer.

Implementation of cursor in SQL

  • We can implement cursor through standard database APIs.
  • We can implement cursor through Transact-SQL

SQL Statement used in cursor

  • DECLARE : It is used to define a new cursor.
  • OPEN : It is used to open a cursor
  • FETCH : It is used to retrieve a row from a cursor.
  • CLOSE : It is used to close a cursor.
  • DEALLOCATE : It is used to delete a cursor and releases all resources used by cursor.

A Statement that is used to create a mcnemp table

create table mcnemp

(

id int,

name varchar(15),

age int,

city varchar(15),

salary money

)

A Statement that is used to insert data in mcnemp table

insert into mcnemp values (1,'vipendra',22,'noida',10000)

insert into mcnemp values (2,'deepak',24,'lucknow',15000)

insert into mcnemp values (3,'rahul',20,'delhi',20000)

insert into mcnemp values (4,'rohit',21,'kanpur',12000)

insert into mcnemp values (5,'shiva',22,'greater noida',13000)

insert into mcnemp values (6,'shivam',18,'noida',14000)

insert into mcnemp values (7,'vip',17,'delhi',17000)

A Statement that show data in mcnemp table

mcnemp.jpg

A Statement that is used to create a cursor

declare cur_ven cursor
static
for
      select vendorid,vendorname,vendorcity
      from mcnvendors
order by vendorid


A Statement that is used to open a cursor

 

 open cur_ven

A Statement that is used to close a close

close cur_ven

A Statement that is used to deallocate a cursor

deallocate cur_ven


© 2020 DotNetHeaven. All rights reserved.