How To Use Cursors And While Loop In SQL Server

Background

Normally, when we need data looping, we use either "Cursors" or "While loop" in SQL Server. Both are used with multiple rows to give decisions on a row-by-row basis.

Looping
Fig: Looping

Create Database

CREATE DATABASE SalesDB;

Here, SalesDB is the name of database

Create Tables

We will create two tables. One is tbl_DailySales and another one is tbl_Product,

tbl_DailySales -

USE[SalesDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE[dbo].[tbl_DailySales](
[DailySaleID][int] IDENTITY(1, 1) NOT NULL, [ProductID][int] NULL, [SalesPrice][decimal](18, 2) NULL, [Discount( % )][decimal](18, 0) NULL, [Description][nvarchar](50) NULL, [Date][datetime] NULL,
CONSTRAINT[PK_tbl_DailySales] PRIMARY KEY CLUSTERED(
[DailySaleID] 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
Demo Data
for tbl_DailySales
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(1, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(2, 2, CAST(5000.00 AS Decimal(18, 2)), CAST(5 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(3, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(4, 3, CAST(3500.00 AS Decimal(18, 2)), CAST(7 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(5, 5, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(6, 4, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(7, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-09 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(8, 2, CAST(5000.00 AS Decimal(18, 2)), CAST(5 AS Decimal(18, 0)), N '', CAST(N '2016-09-10 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(9, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-10 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(10, 3, CAST(3500.00 AS Decimal(18, 2)), CAST(7 AS Decimal(18, 0)), N '', CAST(N '2016-09-10 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(11, 5, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-10 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(12, 4, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(13, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(14, 2, CAST(5000.00 AS Decimal(18, 2)), CAST(5 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(15, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(16, 3, CAST(3500.00 AS Decimal(18, 2)), CAST(7 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(17, 5, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(18, 4, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(19, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(20, 2, CAST(5000.00 AS Decimal(18, 2)), CAST(5 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(21, 1, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(22, 3, CAST(3500.00 AS Decimal(18, 2)), CAST(7 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(23, 5, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO
INSERT[dbo].[tbl_DailySales]([DailySaleID], [ProductID], [SalesPrice], [Discount( % )], [Description], [Date]) VALUES(24, 4, CAST(2000.00 AS Decimal(18, 2)), CAST(10 AS Decimal(18, 0)), N '', CAST(N '2016-09-11 00:00:00.000'
AS DateTime))
GO

tbl_Product

USE[SalesDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE[dbo].[tbl_Product](
[ProductID][int] IDENTITY(1, 1) NOT NULL, [Name][nvarchar](50) NULL, [Grade][nvarchar](10) NULL, [Description][nvarchar](50) NULL,
CONSTRAINT[PK_tbl_Product] PRIMARY KEY CLUSTERED(
[ProductID] 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

Demo Data for tbl_Product

GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(1, N 'Product-1', N 'A', NULL)
GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(2, N 'Product-2', N 'B', NULL)
GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(3, N 'Product-3', N 'A', NULL)
GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(4, N 'Product-4', N 'C', NULL)
GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(5, N 'Product-5', N 'B', NULL)
GO
INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(6, N 'Product-6', N 'A', NULL)
GO

Cursors - Cursor is a database object used by applications to manipulate the data in a set on a row-by-row basis.

Example - Here, we will update the "Discount" column by product "Grade" into tbl_DailySales, using Cursors.

Below is the right query.

Declare @ProductIDint
Declare @Grade nvarchar(50)
Declare ProductCursor CURSOR FOR
selectProductID from[dbo].[tbl_DailySales]
Open ProductCursor
Fetch next from ProductCursor into @ProductID
while(@@FETCH_STATUS=0)
BEGIN
Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
IF(@Grade='A')
BEGIN
updatetbl_DailySales set [Discount(%)]=7 where ProductID=@ProductID
END
Fetch next from ProductCursor into @ProductID
END
Close ProductCursor
DEALLOCATE ProductCursor

Let’s explain the code.

Declare @ProductIDint
Declare @Grade nvarchar(50)

Declare two variables - one is ProductID which is integer type and the other is Grade which is nvarchar type.

Declare ProductCursor CURSOR FOR
selectProductID from [dbo].[tbl_DailySales]

Declare Cursor which is named as ProductCursor, and select ProductID from tble_DailySales’s table into ProductCursor.

Open ProductCursor

Open Coursor,

Fetch next from ProductCursor into @ProductID

Next, row Fetch from ProductCursor.

while(@@FETCH_STATUS=0)

Check FETCH_STATUS when FETCH_STATUS is 0 when it works.

Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)

Select Grade from tbl_product table by ProductId.

IF(@Grade='A')
BEGIN
updatetbl_DailySales set [Discount(%)]=7 where ProductID=@ProductID
END

Check Grade - When Grade is A, the Discount will be updated.

While loop

In SQL Server, we use a WHILE LOOP when we are not sure how many times the loop body will be executed. It is executed on row-by-row basis.

Before going into discussion of while loop, we will discuss about temporary table in SQL. Actually, we use temporary table for using while loop.

Temporary table

Temporary table is very important to keep the data. But it is temporary. Thus, the data gets deleted when the current client session terminates.

The syntax is given below-

CREATE TABLE #TempProduct(
ProductIDint
)
The above script will create a temporary table in TempProduct database. We can insert or delete records in the temporary table similar to a general table like:
Here, we will update Discount column by product Grade into tbl_DailySales using While loop.
Below is the following query
IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL
DROP TABLE #TempProduct
CREATE TABLE #TempProduct(
ID INT IDENTITY(1, 1),
ProductIDint
)
insert into #TempProduct select ProductID from tbl_DailySales
DECLARE @NumberofRowint=(select COUNT(*) from #TempProduct)
DECLARE @inirow INT=1;
Declare @Grade nvarchar(50)
WHILE @inirow<= @NumberofRow
BEGIN
DECLARE @ProductID INT=(select ProductID from #TempProduct where ID=@inirow)
Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
IF(@Grade='A')
BEGIN
updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID
END
SET @inirow = @inirow + 1;
END;

Let’s explain the code.

IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL
DROP TABLE #TempProduct

If TempProduct is available, then this table will be dropped from database.

CREATE TABLE #TempProduct(
ID INT IDENTITY(1, 1),
ProductIDint
)

Create TempProduct table which has two columns - ID and ProductID. Here, ID is primary Key and auto increment.

insert into #TempProduct select ProductID from tbl_DailySales

Insert data from tbl_DailySales into TempProduct’s table.

DECLARE @NumberofRowint=(select COUNT(*) from #TempProduct)
DECLARE @inirow INT=1;
Declare @Grade nvarchar(50)

Count row number from TempProduct’s table and Declare two variable one is inirow and Other is Grade,

WHILE @inirow<= @NumberofRow
BEGIN
DECLARE @ProductID INT=(select ProductID from #TempProduct where ID=@inirow)
Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
IF(@Grade='A')
BEGIN
updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID
END
SET @inirow = @inirow + 1;
END;

Using while loop for looping and other code, we used for update into tbl_DailySales table.

Conclusion

So, in this article, we have seen how to use Cursors and while loop and also create a temporary table and update Discount column by condition.

Hope this will be helpful.