Cursors in SQL Server – SQL Server Tutorial

Cursor in SQL Server   :–

  • A cursor is a pointer to resultset which contains set of records return by the select statement.
  • Using the cursor we can process the record in resultset.
  • cursor are used to process the multiple records.
  • Cursor will reduce no. of trips to database server.

To use cursor follow below steps :-

  1. Declare cursor
  2. Open cursor
  3.  Fetch records from cursor
  4. Close cursor
  5.  Deallocate cursor.

(I) Declare the cursor in SQL Server examples :-

Syntax  :-     Declare<name> cursor[options] for select statement.

Example  :-   Declare c1 cursor for select * from emp.

When cursor is declared ,so context area is created in client side.

(II) Open cursor in SQL Server examples :-

Syntax :-  Open<cursor name>

Example :-     open c1

when cursor is opened

  • (A) The select statement is submitted to database server.
  • (B) The records return by the select statement are loaded in context area.
  • (C) cursor will be pointing to the  that context area.

(III) Fetch records from cursor in SQL Server examples :-

The fetch statement is used to fetch record from the context area and load the record into variables.

Syntax  :- Fetch next/prior/first/last/absolute n/relative n from<cursor> into <variables>

Example  :-  fetch next from c1 into @eno @ename @sal

Fitch statement fitches only are record but process multiple records fetch statement should placed inside the loop.

(IV) Close Cursor in SQL Server examples :-

Syntax  :      close <cursor>

Example  :     close c1.

(V) Deallocate cursor in SQL Server examples :-

Syntax  : deallocate <cursor name>

Example  :-  deallocate c1

The memory allocates for the cursor  is released.

Leave a Reply

Your email address will not be published. Required fields are marked *