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.