Post
Ask Question

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 -
  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,  
  9.   
  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)    
  3.     
  4. Declare ProductCursor CURSOR FOR    
  5. selectProductID from[dbo].[tbl_DailySales]    
  6. Open ProductCursor    
  7. Fetch next from ProductCursor into @ProductID  
  8.   
  9. while(@@FETCH_STATUS=0)  
  10. BEGIN  
  11.   
  12. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)  
  13.   
  14. IF(@Grade='A')  
  15. BEGIN  
  16. updatetbl_DailySales set [Discount(%)]=7 where ProductID=@ProductID  
  17. END  
  18.   
  19.   
  20. Fetch next from ProductCursor into @ProductID  
  21. END  
  22.   
  23. Close ProductCursor   
  24.   
  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. )  
  4.   
  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:  
  6.   
  7. Here, we will update Discount column by product Grade into tbl_DailySales using While loop.  
  8. Below is the following query  
  9.   
  10. IF OBJECT_ID('tempdb..#TempProduct') IS NOT NULL  
  11. DROP TABLE #TempProduct  
  12.   
  13. CREATE TABLE #TempProduct(  
  14. ID INT IDENTITY(1, 1),   
  15. ProductIDint  
  16. )  
  17.   
  18. insert into #TempProduct select ProductID from tbl_DailySales  
  19.   
  20. DECLARE @NumberofRowint=(select COUNT(*) from #TempProduct)  
  21.   
  22. DECLARE @inirow INT=1;  
  23. Declare @Grade nvarchar(50)  
  24.   
  25. WHILE @inirow<= @NumberofRow  
  26. BEGIN  
  27. DECLARE @ProductID INT=(select ProductID from #TempProduct where ID=@inirow)  
  28.   
  29. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)  
  30.   
  31. IF(@Grade='A')  
  32. BEGIN  
  33. updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID  
  34. END  
  35.   
  36.   
  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)  
  2.   
  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)  
  4.   
  5. Set @Grade=(select Grade from tbl_product where ProductID=@ProductID)  
  6.   
  7. IF(@Grade='A')  
  8. BEGIN  
  9. updatetbl_DailySales set [Discount(%)]=51 where ProductID=@ProductID  
  10. END  
  11.   
  12.   
  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.
Next Recommended Reading Write A Query To Print 1 To 100 In SQL Server Using Without Loop