Free Web Development Online Tutorials, Learn to Code
Cursor in sql syntax, Learn SQL Database Development, cursor in SQL tutorial

Cursor in SQL Database

Cursor in SQL Server

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

A cursor is a temporary work area created in the system memory, We normally loop through DML statement inside cursor, let’s look at the example below

Before you write cursor, you need to know following few key characteristic about cursor in SQL database

  • 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
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


Comment
Name
Email
Website
Subscribe