Cursor – About cursors

Cursor – About cursors

What is cursor?

Cursor occupies some memory location for the execution. After completion of cursor execution it will release the memory for the other resource.

-          Avoid to using the cursor

Type of cursor –

-          Static

-          Keyset

-          Dynamic

-          Fast – Forward

Both keyset and static cursor use the work tables build in the tempdb database.

Static cursor store the data in the work table to compare the data,

Keyset cursor stores the keysets to identify the records through keys.

Dynamic cursor reflect all the changes in the result set when cursor scrolling through the rows.

Fast-Forward only cursor fetches the row serially from start to end .It is not supporting the scrolling.

Steps of writing the Cursor –

-          Declare

-          Open

-          Fetch

-          Close

-          De allocate

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM AdventureWorks.HumanResources.vEmployee

WHERE LastName like 'B%';

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM Employee_Cursor

END;

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;

@@FETCH_STATUS Description

Return value                      Description

0                                           The FETCH statement was successful.

-1                                          The FETCH statement failed or the row was beyond the result set.

-2                                          The row fetched is missing.