How to Use Cursors and While loop in SQL Server

In this article we will learn How to Use Cursors and while loop in SQL Server.

Table of Contents
· Background
· Create table
· Cursors
· While loop
· Temporary table
· Conclusion

Background:

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

looping

 

 

 

 

 

 

 

Fig: Looping

Create Database:

Here, SalesDB is name of Database

Create Table:

We will create two Table. Once is tbl_DailySales and other is tbl_Product

tbl_DailySales:

Demo Data for tbl_DailySales

tbl_Product:

Demo Data for tbl_Product

Cursors:

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

Example:

Here, we will update Discount column by product Grade into tbl_DailySales using Cursors.

Below is the following query

Let’s get explain about code:

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

Declare Cursor which name is ProductCursor and select ProductID from tble_DailySales’s table into ProductCursor.

Open Coursor.

Next row Fetch from ProductCursor.

Check FETCH_STATUS when FETCH_STATUS is 0 when it works.

Select Grade from tbl_product table by ProductId.

Check Grade when Grade is A then Discount will be updated.

While loop:

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

Before going to discussion about while loop we will discussion about temporary table in SQL. Actually

We will use temporary table for using while loop.

Temporary table:

Temporary table is very important to keep data. But data is Temporary. Data will be deleted when the current client session terminates.

The syntax given below

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:

Let’s get explain about code:

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

Create TempProduct table which have two columns one is ID and other is ProductID. Here ID is primary Key and auto increment.

Insert data from tbl_DailySales into TempProduct’s table.

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

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 🙂

 

One thought on “How to Use Cursors and While loop in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *