How To Create Cursor In Sql 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 TblDonation Table 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 TblDonation Table, StudentId who donated the amount.

Capture

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

Forward Only Cursor
Static Cursor
Keyset Cursor
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-