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.
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.