How to create Cursor in sql server with example

Welcome to Appsloveworld Technologies, In this post, we’ll discuss Cursors in SQL server. RDMS database management systems are very good at handling data in SETS.

Let’s look at the tables that we’ll be using to understand Cursor. TblStudent Table contains student-related information like the id which is also going to act as the primary key for this table name of the student and FatherName and TblDonationTable contain student donation data.

Every time students donate any amount, an entry will be made into this table. This table contains information like ID, which is going to act as the primary key for the table TblDonationTable, StudentId who donated the amount.

Table Script

/****** Object:  Table [dbo].[TblStudent]    Script Date: 10/28/2020 8:18:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StudentName] [nvarchar](max) NOT NULL,
	[FatherName] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


/****** Object:  Table [dbo].[TblDonation]    Script Date: 10/28/2020 8:18:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblDonation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StudentId] [int] NULL,
	[DonationAmount] [float] NULL,
 CONSTRAINT [PK_TblDonation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO

For example, consider the below update statement.

Update TblDonation Set DonationAmount = 100 where StudentId = 150

All the rows in TblDonation which meet the conditions in the where clause will have the DonationAmount updated to 100 at a single set and SQL server very efficient at operating on data in SETS.

In fact, in SQL statements like UPDATE, delete, select, etc., they handle data and sets in a very efficient way. But however, if there is a need for us to process the rows on a row by row basis, then cursors can be used.

Cursors are very bad for performance and they should always be avoided. Since cursors operate on a row by row basis, they can be extremely slow. But remember, most of the time Cursors can be very easily replaced using joins.

In SQL server So there are different types of cursors

1. Forward Only Cursor
2. Static Cursor 
3. Keyset Cursor
4. Dynamic Cursor

Let’s look at a simple example of using cursors. Now, before we look at cursors, let’s understand what is cursor?cursor is nothing more than a pointer to a row.

Now, let’s say when I execute a select statement, I have ResultSet and I want to process the row within ResultSet on a row by row basis.

I want to process an individual row at a time. so if that’s the case, I can have a pointer or a cursor pointing to ResultSet.

Maybe the cursor is pointing to the first row and if I ask it to give the row, it gives me the first row, and then it moves to the second row and then may I ask it to give the row again.

It’s going to get that row and move to the next or at some point of time when it passes,  when the cursor returns 11th row, there are no more rows within this ResultSet, in which case it doesn’t return anything back.
you can simply understand as a foreach loop.

so let’s see how to use cursors. Now, what I basically want to do is using cursors. I want to print the ID and the StudentName of each row that is present in TblStudent.

Declare @StudentId int
Declare @StudentNanme varchar(100)

Declare StudentCursor CURSOR FOR
select Id,StudentName from TblStudent

Open StudentCursor

Fetch Next from StudentCursor into @StudentId,@StudentNanme

While(@@FETCH_STATUS = 0)
Begin
Print 'Student Id='+cast(@StudentId as varchar(100))+' Student Name='+@StudentNanme
Fetch Next from StudentCursor into @StudentId ,@StudentNanme
End
CLOSE StudentCursor

DEALLOCATE StudentCursor

So I have two variables to hold the ID and the name. we are declaring a cursor using the declared keyword. Declare the name of the cursor and then cursor. we will have a cursor for ResultSet and to get the results that we use to select query.

we have declared the cursor. now when you say Open StudentCursor, what’s going to happen the select statement will be executed and all the rows that match that select statement, the conditions in the where clause are now retrieved into the ResultSet and you will have your cursor pointing just about the first record.it will be ready to retrieve the rows.

Now when you say Fetch Next from StudentCursor into @StudentId,@StudentNanme, what’s going to happen, the cursor will retrieve the first record, the ID into @StudentId, and that name into the @StudentNanme variable.

Remember, the cursor will return one row at a time, so it returns the first row, then it goes to the second row, then the third row, etc..

Now when it has passed all the rows then @@FETCH_STATUS will not be zero. That’s the indication for that.

For us to know that we have processed all the rows. So the FETCH_STATUS will return zero as long as there are rows to be processed.

And look at this. So we are printing the ID name and what do we have again here Fetch Next from StudentCursor into @StudentId,@StudentNanme.

So we have got the first row. Now we are retrieving the second row and we are going to do this as long as there is a row within our ResultSet.

then finally I have a close StudentCursor. So what is this going to do? It’s going to release the resultset and finally, DEALLOCATE will actually DEALLOCATE the resources that are being used by Cursor.

These two statements are very important to close the ResultSet and to DEALLOCATE the resources that I used by the cursor.

Now, let’s say I want to update DonationAmount and there are some conditions to follow to update the DonationAmount in the TblDonation table.

Now, if the student name is ‘Student Name – 150’, then I want to set the DonationAmount to 150.But whereas if the student name is ‘Student Name – 45’,then I want to set the DonationAmount to 45.

Let’s see how to do that. And one important thing to keep in mind is we don’t have the student name in the TblDonation table.

so what we will have to do will create a cursor for TblStudent. For all the rows within TblStudent, and then we will loop through each row, retrieve the StudentId, go to the TblStudent, and then retrieve the name. Check if the name and update the rows.

Declare @StudentId int

Declare StudentDonationCursor CURSOR FOR
select StudentId from TblDonation

Open StudentDonationCursor

Fetch Next from StudentDonationCursor into @StudentId

While(@@FETCH_STATUS = 0)
Begin
Declare @StundetName nvarchar(50)
Select @StundetName = StudentName from TblStudent where Id = @StudentId
if(@StundetName = 'Stundet Name - 150')
Begin
Update TblDonation set DonationAmount = 150 where StudentId = @StudentId
End
else if(@StundetName = 'Stundet Name - 45')
Begin
Update TblDonation set DonationAmount = 45 where StudentId = @StudentId
End

Fetch Next from StudentDonationCursor into @StudentId
End
CLOSE StudentDonationCursor

DEALLOCATE StudentDonationCursor

if you want to verify the update statement then you can use the below query

Select StudentName, DonationAmount
from TblStudent join
TblDonation on TblStudent.Id = TblDonation.StudentId
where (StudentName='Stundet Name - 150' or StudentName='Stundet Name - 45')

If you execute the above query, The cursor will iterate thru each row just like we use the foreach loop in the TblDonation table. As there are 800,000 rows, to be processed on a row-by-row basis.

It takes around 20 seconds in my SQL server management studio.We can easily replace this query using a join, it will increase the performance.
Read More-

The post How to create Cursor in sql server with example appeared first on Software Development | Programming Tutorials.