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.
/****** 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.
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
- how do I reset collation for all columns in the database?
- Translate Excel business logic to T-SQL
- How to delete duplicate (repetitive) records, rows from table without id
- how to fetch same column from different tables
- Openrowset vs. Linked server
- SqlBulkCopy - Unexpected existing transaction
- Transpose of rows and columns using pivot in sql server
- Get Number of open connection string in my application
- UNION ALL - but with non-simultaneous execution of its parts
- Converting an access Last() function into a SQL Query
- Short circuit in IF clause
- Must declare the scalar variable "@AccessLevel". in SQL Server 2016
- Update existing record and insert only new record of the Datagridview using 2 store procedures
- Priority of a query in MS SQL
- If I can ping my DB server, is my SQL Server connection guaranteed to work?
- Select and update SQL
- SQL : in clause in stored procedure:how to pass values
- How I get all Month From Date form sql?
- Update Missing Values
- How to create a LocalDB on Visual Studio 2013 that doesn't require SQL Server installed
- Interpolate Missing Values in SQL Server 2012
- Docker SQL Server exception
- MSSQL - Lowest and 2nd lowest in one row using Row_Number
- Creating SQL Server backup file (.bak) with c# to any location
- Executing SSIS package on SQL server 2005
- Find consecutive sequence and suggest next number in sequence
- how to use field value as condition in sql server query?
- Windows Batch File To Execute SQL Scripts In Ascending Order Of Folder Name
- MSSQL backups - Can I write a script to check if the DB has been modified before backing up?
- SP_Who does not return DBName Column
- Does the user who executes a stored procedure which contains a delete query need delete permission?
- Sql Server Compact Edition database deployment strategy
- Database Design Microsoft SQL Server
- To get total number of columns in a table in sql
- SQL Server 2008 Attendance report for every day of a month
- SSIS expressions using ODBC
- Avoiding code change with Microsoft SQLServer and Unicode
- SQL Server Pivots: Displaying row values to column headers
- Accessing Active Directory Role Membership through LDAP using SQL Server 2005
- What are SSIS packages deployment and change-tracking best practices?
- What's the best way to query a column to see if it contains a particular number? The column is varchar
- Import Large CSV files into SQL Server using C#
- Postgres OpenXML
- Is there a sql only way to turn a hierarchical table into a set of json strings?
- Best practice on sql update statement on NCI dateKey in large Fact table
- get row index in Linq query
- How to run the scripts from a SQL solution in VS
- Sum different row in column based on second column value
- Expand row with an JSON Array inside a Column to multiple rows
- How to remove the first column from a temp table select
- SQL Server - Simultaneous Outcomes
- SQLCMD Mode run with .net windows application
- Milliseconds in my DateTime changes when stored in SQL Server
- Use Views in Entity Framework
- Progressbar for loading data to DataGridView using DataTable
- AWS DMS - Migrating from SQL Server to S3 Bucket. How I can add the header in the .csv?
- Handle backslash in the connection string
- Cannot connect to SQL Server database using pymssql but can connect using underlying freetds tsql
- Insert resultset row in a forEach loop
- Copy an existing row "n" number of times in SQL Server
- Using two aggregate function - min and max on same query
- SSIS Package Error - cannot convert unicode to non unicode
- Why i get error when run the sql server script in c#?
- select top N records for each entity
- Case Statement in Where Clause NULL case
- django panda read sql query map parameters
- dates before 1950 equate future date
- Add column value using scalar valued function
- Unable to replace Char(63) by SQL query
- Duplicate rows, different PK
- How To Save XML Query Results to a File
- Can't connect to Windows Azure Database
- In Microsoft SQL Server, can one database have multiple schemas?
- How to insert geography data to SQL Server with Python jaydebiapi cursor.executemany()?
- Prompt user that input is out of range, based on comparing to sql query
- .NET 4.0 transaction behavior when calling stored procedure
- ssis derived column format
- Inner Join in Specific Sql Query to Linq C#
- Synchronous controller with Task.Factory.StartNew versus Asynchronous controller in MVC 4 versus Service Broker Activation
- Identify of Currently Inserting Row
- design query in editor (left outer join)
- iterating over a temporary table and for each row update another tables row in sql
- If column exists execute one query - otherwise perform the other
- Is there any logic in just maxing tempdb and never having it change size?
- Conditional SQL statement incorrect syntax
- SQL Server 2008R2: SET ANSI_NULLS OFF does not affect merge matching with null values
- SQL query slow calculating cumulative totals
- SQL: select where either column is matched
- Get a single row from multiple rows of a single table
- SQL Server 2012 Multi part identifier could not be bound
- How to use MERGE and then to split one source record to more target records?
- sql composite key with identity
- New permission added to list. Update all users to have new permission via Trigger
- T-SQL SELECT TOP 1
- How to bulk import lat, long from csv file into sql server as spatial data type?
- Run Express service and Angular 6 app in parallel
- Use the same MySQL link for all users?
- Creating a factory method for creating new SqlConnection objects
- LINQ: The cast to value type 'System.Int32' failed because the materialized value is null
- When restoring a backup, how do I disconnect all active connections?
- SQL Server: want to insert data into one column, rest nulls: not letting me
- sp_addlinkedsrvlogin doesn't work but sp_addlinkedserver does?
- SQL query get all duplicate records with count
- Cannot Connect to Mssql database using PHP on shared Namecheap server
- using generated values in instead of triggers
- how should i join these five tables & SUM multiple columns from multiple tables
- Parsing or getting tables from XML type column
- PHP Startup: Unable to load dynamic library 'sqlsrv'
- SQL - Getting duplicate list based on value from multiple column
- Select all users that has a status of UN and their past status
- Return new date string in the same format as input
- Using SQL Server how can I query across three separate tables outer joining on a common column between all three of them?
- Grouped random sample MS SQL Server
- Query to filter where value equals @param unless @param is other
- Difference between NOT LIKE and '[^string]'
- SQLCmd Password parameter with comma
- SQL query assistance with bridge table
- How to guarantee some rows are unique (a sort of secondary key) SQL
- SQL Server convert NULL to empty string in select *
- Posting data to a web page from SQL Server trigger
- Store Sum of rows in another column by Stored Procedure
- How to get the all table(s) name which are used in particular stored procedure?
- SQL BETWEEN Operator
- Compare time part of DateTime data type in SQL Server 2005
- Not Supplying Value to Parameter In SP throwing Error- sql server 2008
- How to remove the top rows of an Excel Spreadsheet on Import to a SQL Server Database
- Not Exists vs Not In: efficiency
- Is there any equivalent method for Oracle's INSERT ALL in SQL Server
- Indexing SQLServer data with SOLR
- Join contents of field and insert new record
- Stored Procedure not working with insert into temp table
- Select one row per JSON element in SQL field / convert JSON field into rows
- I need to pass column names using variable in select statement in Store Procedure but i cannot use dynamic query
- SSDT with large, retail databases?
- How can I Reapeat TextBox in every report page?
- Reset IDENTITY columns Manually
- Complex SQL Query returning unwanted results
- IO Completion Listener (0x1814) Worker 0x0000000000A021A0 appears to be non-yielding on Node 0
- How to get month (in string) diff in sql server
- Left Joining table with values in lookup table
- How to compare data with a multiple selection in SQL Server?
- Insert 100 Record in sql in one query
- Group multiple rows together
- How to return the latest row written for the day?
- SQL Server 2005 XML to table
- SQL join table result of 2 statements
- Order by in Inner Join
- SQL Server query optimization?
- Insert complex XML into SQL Server table
- Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)