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 -
  1. USE[SalesDB]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE TABLE[dbo].[tbl_DailySales](
  8. [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,
  9. CONSTRAINT[PK_tbl_DailySales] PRIMARY KEY CLUSTERED(
  10. [DailySaleID] ASC
  11. ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
  12. ) ON[PRIMARY]
  13. GO
  14. Demo Data
  15. for tbl_DailySales
  16. GO
  17. 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'
  18. AS DateTime))
  19. GO
  20. 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'
  21. AS DateTime))
  22. GO
  23. 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'
  24. AS DateTime))
  25. GO
  26. 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'
  27. AS DateTime))
  28. GO
  29. 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'
  30. AS DateTime))
  31. GO
  32. 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'
  33. AS DateTime))
  34. GO
  35. 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'
  36. AS DateTime))
  37. GO
  38. 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'
  39. AS DateTime))
  40. GO
  41. 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'
  42. AS DateTime))
  43. GO
  44. 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'
  45. AS DateTime))
  46. GO
  47. 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'
  48. AS DateTime))
  49. GO
  50. 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'
  51. AS DateTime))
  52. GO
  53. 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'
  54. AS DateTime))
  55. GO
  56. 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'
  57. AS DateTime))
  58. GO
  59. 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'
  60. AS DateTime))
  61. GO
  62. 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'
  63. AS DateTime))
  64. GO
  65. 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'
  66. AS DateTime))
  67. GO
  68. 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'
  69. AS DateTime))
  70. GO
  71. 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'
  72. AS DateTime))
  73. GO
  74. 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'
  75. AS DateTime))
  76. GO
  77. 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'
  78. AS DateTime))
  79. GO
  80. 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'
  81. AS DateTime))
  82. GO
  83. 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'
  84. AS DateTime))
  85. GO
  86. 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'
  87. AS DateTime))
  88. GO
tbl_Product
  1. USE[SalesDB]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE TABLE[dbo].[tbl_Product](
  8. [ProductID][int] IDENTITY(1, 1) NOT NULL, [Name][nvarchar](50) NULL, [Grade][nvarchar](10) NULL, [Description][nvarchar](50) NULL,
  10. CONSTRAINT[PK_tbl_Product] PRIMARY KEY CLUSTERED(
  11. [ProductID] ASC
  12. ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
  13. ) ON[PRIMARY]
  14. GO
Demo Data for tbl_Product
  1. GO
  2. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(1, N 'Product-1', N 'A', NULL)
  3. GO
  4. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(2, N 'Product-2', N 'B', NULL)
  5. GO
  6. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(3, N 'Product-3', N 'A', NULL)
  7. GO
  8. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(4, N 'Product-4', N 'C', NULL)
  9. GO
  10. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(5, N 'Product-5', N 'B', NULL)
  11. GO
  12. INSERT[dbo].[tbl_product]([ProductID], [Name], [Grade], [Description]) VALUES(6, N 'Product-6', N 'A', NULL)
  13. 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.
  1. Declare @ProductIDint
  2. Declare @Grade nvarchar(50)
  4. Declare ProductCursor CURSOR FOR
  5. selectProductID from[dbo].[tbl_DailySales]
  6. Open ProductCursor
  7. Fetch next from ProductCursor into @ProductID
  9. while(@@FETCH_STATUS=0)
  10. BEGIN
  12. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
  14. IF(@Grade='A')
  15. BEGIN
  16. updatetbl_DailySales set [Discount(%)]=7 where ProductID=@ProductID
  17. END
  20. Fetch next from ProductCursor into @ProductID
  21. END
  23. Close ProductCursor
  25. DEALLOCATE ProductCursor
Let’s explain the code.
  1. Declare @ProductIDint
  2. Declare @Grade nvarchar(50)
Declare two variables - one is ProductID which is integer type and the other is Grade which is nvarchar type.
  1. Declare ProductCursor CURSOR FOR
  2. 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.
  1. IF(@Grade='A')
  2. BEGIN
  3. updatetbl_DailySales set [Discount(%)]=7 where ProductID=@ProductID
  4. 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-
  1. CREATE TABLE #TempProduct(
  2. ProductIDint
  3. )
  5. 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:
  7. Here, we will update Discount column by product Grade into tbl_DailySales using While loop.
  8. Below is the following query
  10. IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL
  11. DROP TABLE #TempProduct
  13. CREATE TABLE #TempProduct(
  14. ID INT IDENTITY(1, 1),
  15. ProductIDint
  16. )
  18. insert into #TempProduct select ProductID from tbl_DailySales
  20. DECLARE @NumberofRowint=(select COUNT(*) from #TempProduct)
  22. DECLARE @inirow INT=1;
  23. Declare @Grade nvarchar(50)
  25. WHILE @inirow<= @NumberofRow
  26. BEGIN
  27. DECLARE @ProductID INT=(select ProductID from #TempProduct where ID=@inirow)
  29. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
  31. IF(@Grade='A')
  32. BEGIN
  33. updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID
  34. END
  37. SET @inirow = @inirow + 1;
  38. END;
Let’s explain the code.
  1. IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL
  2. DROP TABLE #TempProduct
If TempProduct is available, then this table will be dropped from database.
  1. CREATE TABLE #TempProduct(
  2. ID INT IDENTITY(1, 1),
  3. ProductIDint
  4. )
Create TempProduct table which has two columns - ID and ProductID. Here, ID is primary Key and auto increment.
  1. insert into #TempProduct select ProductID from tbl_DailySales
Insert data from tbl_DailySales into TempProduct’s table.
  1. DECLARE @NumberofRowint=(select COUNT(*) from #TempProduct)
  3. DECLARE @inirow INT=1;
  4. Declare @Grade nvarchar(50)
Count row number from TempProduct’s table and Declare two variable one is inirow and Other is Grade,
  1. WHILE @inirow<= @NumberofRow
  2. BEGIN
  3. DECLARE @ProductID INT=(select ProductID from #TempProduct where ID=@inirow)
  5. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)
  7. IF(@Grade='A')
  8. BEGIN
  9. updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID
  10. END
  13. SET @inirow = @inirow + 1;
  14. 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.