Free Web Development Online Tutorials, Learn to Code
Cursor in ms sql syntax, Learn SQL Cursor Loop Development, Cursor in SQL tutorial

MS SQL Cursor Example

In this tutorial you will learn how to create 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 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
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

SQL job Interview Questions Answers

Course in Demand
MS SQL Examples | Join MS SQL Course