Free Web Development Online Tutorials, Learn to Code
Learn SQL Database Development, MS SQL Tutorial Online

Cursor in SQL Database

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

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

declare curTemp cursor This statement will declare a cursor
Open curTemp This will open the cursor
fetch next from curTemp Fetch next while fetch_status=0 is just like a loop statment
Close curTemp Close the cursor
Deallocate curTemp Deallocate will free from memory, just like dispose in C#

Cursor in SQL Database

alter 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
 
Cursor in SQL Database