Cursor in ms sql syntax, Learn SQL Cursor Loop Development, Cursor in SQL tutorial

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.

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
Learn more about SQL Server Cursors

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#

How to create a cursor in sql server

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
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 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
INSERT INTO [tbStudentPayment]
    ,'Admission Request')                
Close curTemp
Deallocate curTemp

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

MS SQL Examples | Join MS SQL Course