Simple SQL Cursor in SQL SERVER with example

SQL Server is most popular Database introduced by Microsoft. Too many SQL Server release available on official Microsoft SQL Server page. Latest release of SQL SERVER up till now is MS SQL SERVER 2014 (Download Link).

What is Cursor ?

Cursor is SQL object to manipulate and retrieve data row by row basis. You can fetch and hold one row at a time. Instead of using while or any looping functions in sql statements you can use Cursor and call it from any where in your sql statement or store procedure. 

Steps to Work with Cursor ?
1 : Declare Cursor
2 : Open
3 : Fetch
4 : Close
5 : Deallocate

We have used NorthWind database(Download Link) for our example. Here we have give you very basic cursor example by which you can clear idea about what is cursor? Initial Step to work with Cursor is to Declare It. In below we have declared cursor cursor_Employee as static cursor. We fetch appropriate fields in cursor which we want.  Now we will open cursor_employee and fetch the records one by one from datatable. In our example we are just printing Employee Full Name and Employee Position, for that We will looping for printing records. We It will Finished we will closed cursor and De-Allocate It. Close and De-allocation is important to release memory.

Sample Code
DECLARE @FirstName varchar(50), @LastName varchar(50),@Position varchar(50)
DECLARE cursor_Employee CURSOR -- Declare cursor
Select FirstName,LastName,Title FROM Employees
OPEN cursor_Employee -- open the cursor

FETCH NEXT FROM cursor_Employee
   INTO @FirstName, @LastName,@Position
    PRINT 'Name:-'+ @FirstName +' '+ @LastName + ', Position:-'+@Position -- print the name

   FETCH NEXT FROM cursor_Employee
   INTO  @FirstName, @LastName,@Position
  PRINT 'Name:-'+ @FirstName +' '+ @LastName + ', Position:-'+@Position -- print the name

CLOSE cursor_Employee -- close the cursor

DEALLOCATE cursor_Employee -- Deallocate the cursor


Popular posts from this blog

12 opensource and free MVC gridview contols

Scheduling background jobs using Hangfire in ASP.NET Core

Difference ExecuteNonQuery vs ExecuteScalar vs ExecuteReader in