How to Create a Visual While Reading?

SQL WHILE loop provides us with the advantage to execute the SQL statement(s) repeatedly until the specified condition result plow out to be false.

In the following sections of this commodity, we volition use more flowcharts in order to explain the notions and examples. For this reason, firstly, we will explain what is a flowchart briefly. The flowchart is a visual geometric symbol that helps to explain algorithms visually. The flowchart is used to simply pattern and document the algorithms. In the flowchart, each geometric symbol specifies different meanings.

The following flowchart explains the essential structure of the WHILE loop in SQL:

Every bit you can see, in each iteration of the loop, the defined condition is checked, then, co-ordinate to the result of the condition, the code flow is determined. If the issue of the status is true, the SQL statement will be executed. Otherwise, the code flow will exit the loop. If any SQL statement exists outside the loop, it volition be executed.

SQL WHILE loop syntax and instance

The syntax of the WHILE loop in SQL looks like equally follows:

WHILE condition Brainstorm { . . . statements . . . } Cease

Afterward these explanations, nosotros will give a very elementary example of a WHILE loop in SQL. In the example given below, the WHILE loop instance will write a value of the variable x times, and and then the loop will be completed:

DECLARE @ Counter INT SET @ Counter = 1 WHILE ( @ Counter <= 10 ) BEGIN Print 'The counter value is = ' + CONVERT ( VARCHAR , @ Counter ) SET @ Counter = @ Counter + 1 END

At present, we will handle the WHILE loop instance line by line and examine it with details.

In this function of the code, we declare a variable, and we assign an initializing value to it:

DECLARE @ Counter INT Fix @ Counter = i

This part of the lawmaking has a specified condition that until the variable value reaches till 10, the loop continues and executes the PRINT statement. Otherwise, the while condition will not occur, and the loop will stop:

In this last part of the code, we executed the SQL statement, and then we incremented the value of the variable:

BEGIN PRINT 'The counter value is = ' + Catechumen ( VARCHAR , @ Counter ) SET @ Counter = @ Counter + ane Cease

The following flowchart illustrates this WHILE loop example visually:

Infinite SQL WHILE loop

In the infinite loop AKA endless loop, the condition issue will never be false, so the loop never ends and can piece of work forever. Imagine that we accept a WHILE loop, and nosotros don't increase the value of the variable. In this scenario, the loop runs endlessly and never ends. Now, we will realize this scenario with the help of the following example. We need to accept account of one affair that we should not forget to cancel the execution of the query manually:

DECLARE @ Counter INT Set @ Counter = 1 WHILE ( @ Counter <= ten ) BEGIN Impress 'Somebody stops me!' END

In the following flowchart, information technology is obvious that the value of the variable never changes; therefore, the loop never ends. The reason for this issue is that the variable is ever equal to ane so the condition returns true for each iteration of the loop:

BREAK statement

BREAK statement is used in the SQL WHILE loop in lodge to leave the electric current iteration of the loop immediately when certain conditions occur. In the more often than not IF…ELSE statement is used to check whether the condition has occurred or not. Refer to the SQL IF Statement introduction and overview article for more details about the IF…ELSE statement.

The following case shows the usage of the Suspension statement in the WHILE loop:

DECLARE @ Counter INT SET @ Counter = i WHILE ( @ Counter <= 10 ) BEGIN PRINT 'The counter value is = ' + CONVERT ( VARCHAR , @ Counter ) IF @ Counter >= vii BEGIN BREAK Finish SET @ Counter = @ Counter + 1 END

In this case, nosotros have checked the value of the variable, and when the value is equal or greater than 7, the code entered the IF…ELSE block and executed the Intermission statement, and so it exited the loop immediately. For this reason, the bulletin shows the values of the variable up to seven. If the condition of the IF…ELSE statement does non meet, the loop will run until the condition result volition exist imitation. The post-obit flowchart explains the working logic of the BREAK argument example as visually:

Go on statement

Go along statement is used in the SQL WHILE loop in order to stop the current iteration of the loop when certain weather condition occur, and so information technology starts a new iteration from the beginning of the loop. Assume that nosotros desire to write only even numbers in a WHILE loop. In gild to overcome this outcome, we can use the CONTINUE statement. In the following example, nosotros will check whether the variable value is odd or even. If the variable value is odd, the lawmaking enters the IF…ELSE statement blocks and increase the value of the variable, execute the Go on statement and starts a new iteration:

DECLARE @ Counter INT Gear up @ Counter = one WHILE ( @ Counter <= 20 ) Brainstorm IF @ Counter % 2 = i Begin SET @ Counter = @ Counter + 1 Keep Finish PRINT 'The counter value is = ' + CONVERT ( VARCHAR , @ Counter ) Ready @ Counter = @ Counter + 1 END

The following flowchart explains the working logic of the Proceed statement case as visually:

Reading table records through the WHILE loop

In the post-obit example, we volition read tabular array information, row by row. Firstly we will create a sample table:

Utilise tempdb GO Driblet TABLE IF EXISTS SampleTable CREATE TABLE SampleTable ( Id INT , CountryName NVARCHAR ( 100 ) , ReadStatus TINYINT ) Go INSERT INTO SampleTable ( Id , CountryName , ReadStatus ) Values ( 1 , 'Germany' , 0 ) , ( 2 , 'France' , 0 ) , ( 3 , 'Italy' , 0 ) , ( 4 , 'Netherlands' , 0 ) , ( v , 'Poland' , 0 ) SELECT * FROM SampleTable

In this step, we will read all data row past row with the help of the WHILE loop:

1 ii iii four 5 half dozen 7 eight ix 10 11 12 13 14 15 16 17

Utilise tempdb GO DECLARE @ Counter INT , @ MaxId INT , @ CountryName NVARCHAR ( 100 ) SELECT @ Counter = min ( Id ) , @ MaxId = max ( Id ) FROM SampleTable WHILE ( @ Counter IS Not NULL AND @ Counter <= @ MaxId ) BEGIN SELECT @ CountryName = CountryName FROM SampleTable WHERE Id = @ Counter PRINT Catechumen ( VARCHAR , @ Counter ) + '. land proper noun is ' + @ CountryName Ready @ Counter = @ Counter + i Cease

In this example, we read the table rows via the WHILE loop. We tin can besides develop more sophisticated and advanced loops based on our needs.

Conclusion

In this article, nosotros learned the SQL WHILE loop with quite unproblematic examples. We also virtualized and explained the examples with flowcharts. WHILE loop helps u.s. to achieve iterative operations in SQL Server. At the same time, BREAK and Keep statements can be used to command iteration of the WHILE loop in SQL Server.

• Author
• Contempo Posts

Esat Erkec is a SQL Server professional who began his career viii+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Evolution. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

carrollgrevinat.blogspot.com

Source: https://www.sqlshack.com/sql-while-loop-with-simple-examples/