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
- 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-
The post How to create Cursor in sql server with example appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?