How To Create Cursor in Sql Server With Example

Welcome to Quickpickdeal Technologies. In this post, we will discuss Cursors in SQL Server. Relational Database Management Systems (RDMS) excel at handling data in sets.

Let’s examine the tables that we will use to understand Cursors. The TblStudent table contains student-related information such as the ID, which also serves as the primary key for this table, the name of the student, and the father's name. The TblDonationTable contains data related to student donations.

Each time a student makes a donation, an entry is recorded in this table. It includes information such as the ID, which acts as the primary key for the TblDonationTable, and the StudentId of the donating student.

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 that meet the conditions in the WHERE clause will have their DonationAmount updated to 100 in a single operation. SQL Server is highly efficient at handling data in sets.

Indeed, in SQL statements like UPDATE, DELETE, SELECT, etc., the system operates efficiently on data in sets. However, if there arises a need to process rows one by one, cursors can be utilized.

It's importent to note that cursors significantly impact performance negatively and should be avoided whenever possible. Because cursors work row by row, they can be exceptionally slow. Nonetheless, in most cases, cursors can be 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 straightforward example of using cursors. But before delving into cursors, let’s clarify what a cursor is?. A cursor is essentially a pointer to a row.

Now, imagine I execute a SELECT statement and obtain a ResultSet. If I wish to process each row within the ResultSet individually, on a row-by-row basis, I would utilize a cursor.

I aim to process one row at a time. In such a scenario, I can utilize a pointer or a cursor directed at the ResultSet.

For instance, the cursor may initially point to the first row. Upon requesting the row, it provides the first row, then proceeds to the second row, and so forth. If prompted for the row again, it retrieves the next row. Eventually, when the cursor reaches the 11th row, if there are no more rows within the ResultSet, it returns nothing.

Conceptually, this process resembles a foreach loop.

Now, let's explore how to employ cursors. Essentially, I want to utilize cursors to print the ID and StudentName of each row 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, we have two variables to store the ID and the name. We declare a cursor using the 'DECLARE' keyword, specifying the name of the cursor and the SELECT query to retrieve results.

Once the cursor is declared, executing 'OPEN StudentCursor' triggers the execution of the SELECT statement. This fetches all the rows that match the conditions specified in the WHERE clause into the ResultSet, positioning the cursor at the first record, ready to retrieve rows.

Subsequently, executing 'FETCH NEXT FROM StudentCursor INTO @StudentId, @StudentName' retrieves the first record, assigning its ID to @StudentId and its name to @StudentName.

It's important to note that the cursor returns one row at a time, progressing from the first row to the last.

When all rows have been processed, '@@FETCH_STATUS' will not be zero, indicating that there are no more rows to retrieve. This serves as an indication that all rows have been processed. '@@FETCH_STATUS' returns zero as long as there are rows remaining to be processed.

Now, let's observe this process. We print the ID and name, and then we fetch the next row from the StudentCursor into @StudentId, @StudentName.

After fetching the first row, we proceed to retrieve the second row, continuing this process as long as there are rows within our ResultSet.

Finally, we close the StudentCursor. This action releases the ResultSet. Additionally, using DEALLOCATE deallocates the resources utilized by the cursor. These two statements are crucial for closing the ResultSet and deallocating cursor resources.

Now, suppose we want to update the DonationAmount based on certain conditions in the TblDonation table. For instance, if the student name is 'Student Name - 150', we set the DonationAmount to 150. Conversely, if the student name is 'Student Name - 45', we set the DonationAmount to 45.

It's important to note that the TblDonation table doesn't contain the student name. Hence, we need to create a cursor for TblStudent. We loop through each row, retrieve the StudentId, access TblStudent to retrieve the name, check if it matches our criteria, and update the rows accordingly.

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')

When executing the above query, the cursor will iterate through each row, similar to how we use a foreach loop, in the TblDonation table. With 800,000 rows to process individually, it takes approximately 20 seconds in my SQL Server Management Studio.

We can significantly enhance performance by replacing this query with a join operation.