In this article I will explain with an example, how to use WHILE LOOP with Cursor in
SQL Server.
Cursor is supported in following
SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.
Database
I have made use of the following table Customers with the schema as follows.
![Using WHILE loop with Cursor in SQL Server]()
I have already inserted few records in the table.
![Using WHILE loop with Cursor in SQL Server]()
Note: You can download the database table SQL by clicking the download link below.
What are Cursors in SQL Server?
A Cursor is a Database object which allows us to process each row and manipulate its data.
A Cursor is always associated with a SELECT query and it will process each row returned by the SELECT query one by one.
Using Cursor we can verify each row data, modify it or perform calculations which are not possible when we get all records at once.
A simple example would be a case where you have records of Employees and you need to calculate Salary of each employee after deducting Taxes and Leaves.
Syntax for writing Cursors in SQL Server
Following image describes the syntax for writing a Cursor in
SQL Server.
![Using WHILE loop with Cursor in SQL Server]()
Writing a Cursor with WHILE LOOP in SQL Server
The following Cursor is a READ_ONLY Cursor.
Note: In this article, only READ_ONLY Cursors are discussed.
The Cursor is begin by declaring some variables for storing the fetched column data for the table.
Then, the Cursor is declared with a name and its type is set as READ_ONLY and along the FOR keyword the SELECT query is written.
Once the Cursor is setup, it is opened using the OPEN command and the first record is fetched into the variables.
Note: Whenever a record is fetched the @@FETCH_STATUS has value 0 and as soon as all the records returned by the SELECT query are fetched, its value changes to -1.
The Cursor is associated with a WHILE loop which executes until the @@FETCH_STATUS value becomes 0.
Inside the WHILE loop, the processing is done for the current record and then again the next record is fetched and the records are printed.
Finally, the Cursor is closed and deallocated using CLOSE and DEALLOCATE commands respectively.
Note: It is very important to
deallocate a Cursor, otherwise it will stay in database and when you declare another Cursor with same name again,
SQL Server will throw an error:
A cursor with the name 'Cursor1' already exists.
--DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @CustomerId INT
,@Name VARCHAR(100)
,@Country VARCHAR(100)
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1
--DECLARE THE CURSOR FOR A QUERY.
DECLARE PrintCustomers CURSOR READ_ONLY
FOR
SELECT CustomerId, Name, Country
FROM Customers
--OPEN CURSOR.
OPEN PrintCustomers
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO
@CustomerId, @Name, @Country
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Counter = 1
BEGIN
PRINT 'Customer Id' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'Country'
PRINT '------------------------------------'
END
--PRINT CURRENT RECORD.
PRINT CAST(@CustomerId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @Country
--INCREMENT COUNTER.
SET @Counter = @Counter + 1
--FETCH THE NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO
@CustomerId, @Name, @Country
END
--CLOSE THE CURSOR.
CLOSE PrintCustomers
DEALLOCATE PrintCustomers
Disadvantages of Cursor
The major disadvantage of a Cursor is its performance issue.
A Cursor can be really slow when performing operations on large number of records and your SQL query may take minutes to execute and produce results.
Thus, you must wisely choose and decide on the right scenario where you want to use a Cursor.
Screenshot
Records printed by Cursor
![Using WHILE loop with Cursor in SQL Server]()
Downloads
