WHILE loop in SQL Server

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained the implementation about how to use or work WHILE loop in SQL Server.  For this work here I am going to use Cursor in SQL Server. Cursor is supported for mentioned SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.

Database

Implementation: For the explanation about this article, I created table named  userdetails with the schema as follows. I already inserted some records in the table named  userdetails.

Here I am also giving SQL Server Database Script, you can execute this SQL Database Script by using copy and paste to create table with column and data.

         SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[userdetails](

            [userid] [int] IDENTITY(1,1) NOT NULL,

            [username] [varchar](100) NOT NULL,

            [useremail] [varchar](50) NOT NULL,

            [useraddress] [varchar](250) NOT NULL,

 CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [user_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

INSERT INTO Customers

SELECT 'Sanjeev', 'sanjeen@gmail.com', ‘#250 Sector 127, Chandigarh’

UNION ALL

SELECT 'Sanjay', 'sanjay@gmail.com', ‘#751 Sector 186, Chandigarh’

UNION ALL

SELECT 'Ruhika', 'ruhika@gmail.com', ‘#256 Sector 855, Chandigarh’

UNION ALL

SELECT 'Rajan', 'rajan@gmail.com', ‘#55 Sector 00, Chandigarh’

 

Cursors in SQL Server

A cursor in SQL Server is a database object that allows for row-by-row processing of a result set. While SQL typically operates on sets of data, cursors provide a mechanism to iterate through individual rows, enabling procedural logic and specific operations on each row. A Cursor is every time associated with a SQL Server Select query and this process will work and each row will be returned by the SQL Server Select one by one. Using Cursor we can do verification of each row of fetched data, and we can also modify each row of fetched data or we can also do the calculations which are not possible when we get all records at once. I am explain with simple example using a case where you have records of users or employee and you need to calculate Salary of each employee after deducting recovery, taxes, leaves etc.

Writing a Cursor with WHILE LOOP in SQL Server

Here I am using the Cursor is a READ_ONLY Cursor. So in this article, we will discuss about only READ_ONLY Cursors. The Cursor will begin by declaring some variables for storing or holding the data of fetched column of the mentioned table. After that we will is declared Cursor with their name and we set it READ_ONLY and with the FOR keyword the SQL Select query will be write. After the setup of cursor, it will be opened by using the OPEN command and the first record will be fetched into the variables. Whenever a record will be 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. Here the Cursor is connected with a WHILE loop which will be execute until the @@FETCH_STATUS value becomes 0. Inside the WHILE loop, the process will be done for the current record and then again the next record is fetched and the records will be printed one by one. Finally, we will close the Cursor and also deallocate it using CLOSE and DEALLOCATE commands. It is compulsory to deallocate a Cursor, otherwise it will be still available in database and when you will try to declare another one or next  Cursor with same name again, SQL Server will give an error: “ A cursor with the name 'Your mentioned name' already exists.“

 -- Here I am declaring the variable to store the data or hold the data.

DECLARE @userid INT

      ,@username VARCHAR(100)

      ,@useremail VARCHAR(100)

      ,@useraddress VARCHAR(100)

 

 --Here I am declaring the set counter as integer and set the starting value as 1.

DECLARE @count_value INT

SET @count_value = 1

 -- Here I am declaring the Cursorr for executing the Query.

DECLARE printuserdetails CURSOR READ_ONLY

FOR

SELECT userid, username, useremail,useraddress

FROM userdetails

-- Here CURSOR is opening..

OPEN printuserdetails

 

-- Here I am fetching the records or data in the declared variables.

FETCH NEXT FROM printuserdetails INTO

@userid, @username, @useremail,@useraddress

 --Here while loop will be execute or working until records are avaibale as per the select query. WHILE @@FETCH_STATUS = 0

BEGIN

      IF @Counter = 1

      BEGIN

            PRINT 'User Id' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'Email'+ CHAR(9) + 'Address'

            PRINT '------------------------------------'

      END

 --Here I am printing the fetched records.

      PRINT CAST(@userid AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @username + CHAR(9) + @useremail+ CHAR(9) + @useraddress

     

     --here the declared counter will be incremented one by one until loop not over.

      SET @count_value = @count_value + 1

 

      --Here i am fetching the next records as per the loop.

      FETCH NEXT FROM PrintCustomers INTO

     @userid, @username, @useremail,@useraddress

END

 

--Here I am closing the cursor after using.

CLOSE printuserdetails

DEALLOCATE printuserdetails

 

Disadvantages of Cursor

SQL cursors, while providing row-by-row processing capabilities, come with several disadvantages. Cursors are generally slower than set-based operations. They process data one row at a time, incurring overhead for each operation (fetching, positioning, retrieving), which significantly impacts performance, especially with large datasets. Cursors can consume more server resources, including memory and temporary storage (like tempdb in SQL Server), to manage the result set and maintain cursor state. This can lead to resource contention and impact overall database performance. Cursors can hold locks on data for extended periods, potentially leading to blocking and reduced concurrency in multi-user environments. This can degrade the responsiveness of other database operations.

Conclusion: In above code, I have been explained implementation about explained the implementation about how to use or work WHILE loop in SQL Server. This code is very helpful for every developer. Bye and take care of yourself Developers. We will come back shortly with the new article.

Regards

Using Asp.net