Online Training for Asp.net MVC Web Designing Development, MS SQL, Digital Marketing

Learn 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

Group Training