SQL Cursor Example

In this tutorial you will learn what is cursor and how to create cursor in SQL database.

A cursor in SQL server- is a temporary working area created in sql server system memory when a cursor is executed, Cursor is very useful when we want to manipulate data, but can change only one row at a time, so we hold data in temporary memory location then loop through each row one by one.

declare @QueryId bigint,
    @StudentId bigint,
    @PaymentId bigint,
    @RequestStatus int,
    @PaymentStatus int


declare curTemp cursor for

/*fetch data based on your query and where clause*/
SELECT  StudentRequestId,
    RequestStatus,
    RequestStatus   
    FROM tbStudentPayment 

// open cursor
 Open  curTemp
        
    fetch next from curTemp 
    into @QueryId, @PaymentStatus, @RequestStatus
    while @FETCH_STATUS =0

    begin
        // do anything with values from variable
        // select  @QueryId, @StudentId
    END
Close curTemp
Deallocate curTemp

Above code will help fetching records from tbStudentPayment table one by one, and based on payment status we can perform further update for each record.

Cursor in SQL Server

Let's learn about Cursor in SQL database with an example

There are four type of cursors in sql server:

1. Forward-only
2. Static
3. Dynamic
4. Keyset

We normally loop through DML statement inside cursor, before you write cursor, you need to know following few key characteristic of SQL Cursor

• Declaring the Cursor
  declare cursorName cursor:

  declare curTemp cursor

  This statement will declare a cursor

• Opening the Cursor
  Open cursorName:

  Open  curTemp

  This will open the cursor

• Fetching the Cursor
  fetch next from cursorName:

  fetch next from curTemp

  Fetch next while fetch_status=0 is just like a loop statment

• Closing the Cursor
  Close cursorName:

  Close curTemp

  Close the cursor

• deallocate cursor
  Deallocate cursorName:

  Deallocate curTemp

  Deallocate will free from memory, just like dispose in C#

Here in example we create a cursor with name “curTemp”, but before we start writing the cursor let’s get familiar with following keys

Here we have written this cursor within a stored procedure, when we execute the stored procedure the cursor will be executed automatically, also notice how we can use cursor in sql for loop in example below

create procedure uspInitiateAdmission As
BEGIN
declare @QueryId bigint,
@StudentId bigint,
@PaymentId bigint,
@RequestStatus int,
@PaymentStatus int
declare curTemp cursor for
/*fetch data based on your query and where clause*/
SELECT t2.StudentRequestId,
t2.RequestStatus,
t2.RequestStatus FROM tbStudentPayment t1 right outer JOIN tbSchoolManagementBoard t2
ON t1.QueryId = t2.StudentRequestId
where t2.RequestStatus <> 4 and
DATEADD(dd, 3, t2.RequestDateTime) >= GETDATE() and
    t2.StudentRequestId not in (select QueryId from tbStudentPayment)

    Open  curTemp
        fetch next from curTemp 
into @QueryId, @PaymentStatus, @RequestStatus
while @FETCH_STATUS =0
begin
INSERT INTO [tbStudentPayment]
    ([QueryId]
    ,[StudentId]
    ,[PaymentId]     
    ,[ActionDate]
    ,[Status])
VALUES
    (@QueryId
    ,@StudentId
    ,1
    ,GETDATE()
    ,'Admission Request')                
END
Close curTemp
Deallocate curTemp
END

Note: above cursor example written inside and stored procedure for a business requirement , that’s not necessary, you can write cursor anywhere

However, the advantage of writing the cursor code inside stored procedure is, we can simply execute the stored procedure whenever we want the cursor code to run.

You may be interested in following posts

• SQL UDF Function Example
• SQL Trigger Example
• SQL table Foreign Key Concept