SQL Server – Cursor with simple example

SQL Server – Cursor

Sql server can provide the complete result set using cursor that can be process one row at time. Cursor used to retrieve data from tables and stores in variable for any modification.

Cursor Syntax

Declare @variablename varchar(10)

FOR [select_query]
OPEN [Cursorname]
FETCH INTO [@variablename]
WHILE @@fetch_status = 0

FETCH NEXT FROM [Cursorname] INTO [@variablename]
CLOSE [Cursorname]
DEALLOCATE [Cursorname]

Here, Cursor always start with declaration

Declare -> Declare a variable to hold value from cursor

How cursor executes in sql server ?

DECLARE CUSROR -> Using this statement you can declare cursor name

FOR -> For indicates the select query to retrieve data from different using cursor.

OPEN -> Open the cursor to read data

FETCH INTO -> Using FETCH INTO keyword, you can store values in variables

WHILE @@fetch_status = 0 -> Indicates read the values till result set is come zero or reaches last value in while.

FETCH NEXT FROM [Cursorname] INTO [@variablename] -> Inside while, you can move forward using fetch next keyword.

END -> End of while loop

CLOSE [Cursorname] -> Closing the cursor

DEALLOCATE [Cursorname] -> Removing or deallocating memory for cursor

Simple Cursor example

how to use cursor in sql server with example

use testdb
Declare @EmployeeName varchar(200)

Declare FetchEmployeeDetail cursor
For select name from employee where employee_dept = ‘IT’
Open FetchEmployeeDetail
Fetch next from FetchEmployeeDetail
Into @EmployeeName

While @@fetch_status = 0

Select @EmployeeName

Fetch next from FetchEmployeeDetail into @EmployeeName
Close FetchEmployeeDetail
Deallocate FetchEmployeeDetail

Leave a Reply




2 + 4 =