Cet article vous présentera un aperçu complet de l'utilisation de la boucle WHILE dans SQL Server. UN La boucle WHILE est une instruction de flux de contrôle utilisée pour exécuter à plusieurs reprises l'ensemble d'instructions jusqu'à ce que la condition spécifiée soit satisfaite. . Cette boucle commence par une condition donnée, évaluez-la, et si elle est VRAIE, les instructions iront à l'intérieur de la boucle pour une exécution ultérieure. Si la condition devient FALSE, il ne s'exécutera pas. Cela implique que la boucle while dans SQL Server peut s'exécuter zéro ou plusieurs fois.
Organigramme de la boucle WHILE
L'organigramme suivant expliquera le flux de travail complet de la boucle WHILE dans SQL Server :
fonction prototype c++
Nous pouvons voir dans ce graphique que la condition spécifiée est vérifiée pour chaque itération et, en fonction du résultat de l'évaluation, le flux de code est déterminé. Si le résultat est évalué TRUE, le flux de contrôle entre dans la boucle pour une exécution ultérieure. Si le résultat évalué est FALSE, le flux de contrôle quittera la boucle et toute instruction ou requête en dehors de la boucle sera exécutée.
Syntaxe
La syntaxe suivante illustre la boucle WHILE dans SQL Server :
WHILE boolean_condition BEGIN BREAK END;
Dans cette syntaxe, nous avons les paramètres ou arguments suivants :
Exemple de boucle WHILE
Comprenons comment fonctionne la boucle WHILE dans SQL Server à travers un exemple. Dans l'exemple donné, nous avons d'abord déclaré une valeur de type entier et définissez sa valeur sur 1. Ensuite, la boucle WHILE vérifie la condition, et si elle est VRAI , la déclaration d'impression sera imprimée. Quand la boucle devient FAUX , l'instruction suivante après la boucle WHILE sera imprimée.
DECLARE @stud_value INT; SET @stud_value = 1; WHILE @stud_value <= 5 begin print 'mark henry'; set @stud_value="@stud_value" + 1; end; 'rose bennet'; < pre> <p>Executing this statement will return the following output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-2.webp" alt="SQL Server WHILE LOOP"> <p>In the above WHILE loop code snippet, we must increment the variable's value after each iteration. See the below part of the above code line as <strong>SET @stud_value = @stud_value + 1</strong> . If we do not write this statement, the loop will execute infinitely because it cannot becomes FALSE.</p> <pre> BEGIN PRINT 'Mark Henry'; SET @stud_value = @stud_value + 1; END; </pre> <h3>Infinite WHILE Loop</h3> <p>An infinite loop occurs when the evaluation of a condition will never be false. Therefore, the loop will never end and be executed forever. The loop in the following code snippet is infinite because the variable's value is not incremented.</p> <pre> DECLARE @stud_value INT; SET @stud_value = 1; WHILE @stud_value <= 5 begin print 'please stop execution!' end; < pre> <p>Executing the loop will display the below output. This loop will never end its execution until we do not cancel their execution of the query manually.</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-3.webp" alt="SQL Server WHILE LOOP"> <h3>Inserting Records with WHILE Loop</h3> <p>We can also use the WHILE loop to insert records into the defined table. Let us see how to inserts dummy records into the database. First, we will create a table named <strong>'bikeshop'</strong> containing three columns: <strong>Id, bike_name,</strong> and <strong>price</strong> . Execute the following statement to create this table:</p> <pre> CREATE TABLE bikeshop ( Id INT PRIMARY KEY IDENTITY, bike_name VARCHAR (50) NOT NULL, price FLOAT ) </pre> <p>Next, we will use the WHILE loop to insert ten records into this table by executing the following script:</p> <pre> DECLARE @count INT; SET @count = 1; WHILE @count <= 10 begin insert into bikeshop values('bike-' + cast(@count as varchar), @count*5000) set @count="@count" 1; end; < pre> <p>In this code, we have declared a variable @ <strong>count</strong> and then initialize its value with 1 using a SET clause. Next, we have to define the loop body that executes the INSERT statement to add one record in each execution. The <strong>bike_name column</strong> will append the value of a @count variable with the string <strong>Bike</strong> , and the <strong>price</strong> column determines by the value of a @count variable multiplied by <strong>5000</strong> . The loop will execute until the value of the @count variable becomes FALSE. It means the WHILE loop will execute ten times and <strong>inserts ten records</strong> into the table bikeshop.</p> <p>Now, we can verify all the records of the bikeshop table with the SELECT statement. It will display the following output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-4.webp" alt="SQL Server WHILE LOOP"> <h3>BREAK Statement</h3> <p>SQL Server also allows us to use the BREAK statement in the WHILE loop like programming languages. This statement is used to <strong>immediately stop the current iteration of the loop</strong> , and control flow resumes with the next statement after the loop. In general, we will use the <a href="/sql-server-if-else"> <strong>IF...ELSE statement</strong> </a> to check whether or not a condition has occurred.</p> <p>The following example will explain how to use the BREAK statement in the WHILE loop:</p> <pre> DECLARE @count INT; SET @count = 1; WHILE @count = 6 BEGIN BREAK END SET @Count = @Count + 1 END; </pre> <p>Executing the code will display the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-5.webp" alt="SQL Server WHILE LOOP"> <p>The value of the variable is first evaluated in this code. If it is TRUE, the control enters into the loop and prints the statement. When the variable value is greater than or equal to 6, control enters the IF...ELSE block and executes the BREAK statement to terminate the loop. If an IF...ELSE block fails to meet the condition; then, the loop will keep running until the condition is changed to FALSE.</p> <h3>CONTINUE Statement</h3> <p>SQL Server also allows us to use the CONTINUE statement in the WHILE loop like programming languages. This statement immediately <strong>terminates the current execution of the loop when the specified condition is met</strong> , and control flow returns to the beginning of the loop. In general, the IF...ELSE statement will be used to test whether or not a condition has been met.</p> <p>The CONTINUE statement in the WHILE loop is demonstrated in the following example. In this example, we'll assume that we wish to use a WHILE loop to <strong>print only odd values</strong> . The CONTINUE statement can be used to do this. This example will first <strong>test</strong> whether the variable value is <strong>odd or even</strong> . If it is even, the execution goes inside the IF…ELSE statement blocks and decrement the variable value by one. Then, it will execute the CONTINUE statement and starts a new iteration from the beginning.</p> <pre> DECLARE @Count INT SET @Count = 1 WHILE (@Count <= 1 2="0" 44 20) begin if @count % set + continue end print 'the odd value is=" + CONVERT(VARCHAR, @Count) SET @Count = @Count + 1 END </pre> <p>Executing the code snippet will display the below output:</p> <img src=" techcodeview.com img sql-server-tutorials sql-server-while-loop-6.webp' alt="SQL Server WHILE LOOP"> <h3>How to implementing paging with WHILE loop in SQL Server?</h3> <p>We can also use the WHILE loop for implementing the paging. Paging allows displaying the subset of records from a table at any particular time. The following example will explain this concept. The WHILE loop in the code will select two records from the bikeshop table at a time. The records that have been chosen are then displayed in the output.</p> <pre> DECLARE @count INT DECLARE @limit INT; SET @count = 0 SET @limit = 2; WHILE @count <10 begin select * from bikeshop order by id offset @count rows fetch next @limit only set + 2; end; < pre> <p>Executing the code snippet will return the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-7.webp" alt="SQL Server WHILE LOOP"> <h3>Nested WHILE Loop</h3> <p>The Nested WHILE Loop in SQL Server is simply a WHILE Loop written inside another WHILE Loop. When we work on multi-layered data, the Nested WHILE loops are essential. Because this concept is useful in extracting the layered data when we want to select them, it is recommended to be careful while using the nested loop.</p> <p> <strong>Syntax</strong> </p> <p>The following syntax illustrate the working of the nested WHILE Loop in SQL Server:</p> <pre> WHILE Expression BEGIN WHILE @Val2 <= 10 begin --second while loop statements sql end --this statement is outside the second --which first -- this < pre> <p>Let us explain this syntax step by step:</p> <p> <strong>Step 1:</strong> The loop starts by checking the first WHILE loop condition, and if it finds a false result, it will exit from While Loop. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution. This block will start the execution of the second WHILE loop. See step 2.</p> <p> <strong>Step 2:</strong> This step will check the condition in the Nested WHILE Loop, and if it is false, the second loop will be exit and execute the statement outside this. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution.</p> <p> <strong>Step 3:</strong> Once all the statements execute from the second WHILE loop, the control goes to the first WHILE and repeats the first step.</p> <p> <strong>Example</strong> </p> <p>The following example will print the multiplication table of 5 up to 10 using the nested WHILE loop.</p> <pre> DECLARE @val1 INT DECLARE @val2 INT SET @val1 = 5 SET @val2 = 1 WHILE @val1 <= 5 44 begin while @val2 <="10" print convert(varchar, @val1) + ' * @val2) techcodeview.com img sql-server-tutorials sql-server-while-loop-8.webp' alt="SQL Server WHILE LOOP"> <h2>Conclusion</h2> <p>The WHILE loop is a useful method when there is a need to execute a SQL script repeatedly. The article explained how to work with the WHILE loop in MS SQL Server to execute operations such as record insertion and pagination with a simple example. Here we have also learned the BREAK and CONTINUE statements to control the WHILE loop iteration.</p> <hr></=></pre></=></pre></10></pre></=></pre></=></pre></=></pre></=> Boucle WHILE infinie
Une boucle infinie se produit lorsque l'évaluation d'une condition ne sera jamais fausse. Par conséquent, la boucle ne se terminera jamais et sera exécutée pour toujours. La boucle dans l'extrait de code suivant est infinie car la valeur de la variable n'est pas incrémentée.
DECLARE @stud_value INT; SET @stud_value = 1; WHILE @stud_value <= 5 begin print \'please stop execution!\' end; < pre> <p>Executing the loop will display the below output. This loop will never end its execution until we do not cancel their execution of the query manually.</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-3.webp" alt="SQL Server WHILE LOOP"> <h3>Inserting Records with WHILE Loop</h3> <p>We can also use the WHILE loop to insert records into the defined table. Let us see how to inserts dummy records into the database. First, we will create a table named <strong>'bikeshop'</strong> containing three columns: <strong>Id, bike_name,</strong> and <strong>price</strong> . Execute the following statement to create this table:</p> <pre> CREATE TABLE bikeshop ( Id INT PRIMARY KEY IDENTITY, bike_name VARCHAR (50) NOT NULL, price FLOAT ) </pre> <p>Next, we will use the WHILE loop to insert ten records into this table by executing the following script:</p> <pre> DECLARE @count INT; SET @count = 1; WHILE @count <= 10 begin insert into bikeshop values(\'bike-\' + cast(@count as varchar), @count*5000) set @count="@count" 1; end; < pre> <p>In this code, we have declared a variable @ <strong>count</strong> and then initialize its value with 1 using a SET clause. Next, we have to define the loop body that executes the INSERT statement to add one record in each execution. The <strong>bike_name column</strong> will append the value of a @count variable with the string <strong>Bike</strong> , and the <strong>price</strong> column determines by the value of a @count variable multiplied by <strong>5000</strong> . The loop will execute until the value of the @count variable becomes FALSE. It means the WHILE loop will execute ten times and <strong>inserts ten records</strong> into the table bikeshop.</p> <p>Now, we can verify all the records of the bikeshop table with the SELECT statement. It will display the following output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-4.webp" alt="SQL Server WHILE LOOP"> <h3>BREAK Statement</h3> <p>SQL Server also allows us to use the BREAK statement in the WHILE loop like programming languages. This statement is used to <strong>immediately stop the current iteration of the loop</strong> , and control flow resumes with the next statement after the loop. In general, we will use the <a href="/sql-server-if-else"> <strong>IF...ELSE statement</strong> </a> to check whether or not a condition has occurred.</p> <p>The following example will explain how to use the BREAK statement in the WHILE loop:</p> <pre> DECLARE @count INT; SET @count = 1; WHILE @count = 6 BEGIN BREAK END SET @Count = @Count + 1 END; </pre> <p>Executing the code will display the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-5.webp" alt="SQL Server WHILE LOOP"> <p>The value of the variable is first evaluated in this code. If it is TRUE, the control enters into the loop and prints the statement. When the variable value is greater than or equal to 6, control enters the IF...ELSE block and executes the BREAK statement to terminate the loop. If an IF...ELSE block fails to meet the condition; then, the loop will keep running until the condition is changed to FALSE.</p> <h3>CONTINUE Statement</h3> <p>SQL Server also allows us to use the CONTINUE statement in the WHILE loop like programming languages. This statement immediately <strong>terminates the current execution of the loop when the specified condition is met</strong> , and control flow returns to the beginning of the loop. In general, the IF...ELSE statement will be used to test whether or not a condition has been met.</p> <p>The CONTINUE statement in the WHILE loop is demonstrated in the following example. In this example, we'll assume that we wish to use a WHILE loop to <strong>print only odd values</strong> . The CONTINUE statement can be used to do this. This example will first <strong>test</strong> whether the variable value is <strong>odd or even</strong> . If it is even, the execution goes inside the IF…ELSE statement blocks and decrement the variable value by one. Then, it will execute the CONTINUE statement and starts a new iteration from the beginning.</p> <pre> DECLARE @Count INT SET @Count = 1 WHILE (@Count <= 1 2="0" 44 20) begin if @count % set + continue end print \'the odd value is=" + CONVERT(VARCHAR, @Count) SET @Count = @Count + 1 END </pre> <p>Executing the code snippet will display the below output:</p> <img src=" techcodeview.com img sql-server-tutorials sql-server-while-loop-6.webp\' alt="SQL Server WHILE LOOP"> <h3>How to implementing paging with WHILE loop in SQL Server?</h3> <p>We can also use the WHILE loop for implementing the paging. Paging allows displaying the subset of records from a table at any particular time. The following example will explain this concept. The WHILE loop in the code will select two records from the bikeshop table at a time. The records that have been chosen are then displayed in the output.</p> <pre> DECLARE @count INT DECLARE @limit INT; SET @count = 0 SET @limit = 2; WHILE @count <10 begin select * from bikeshop order by id offset @count rows fetch next @limit only set + 2; end; < pre> <p>Executing the code snippet will return the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-7.webp" alt="SQL Server WHILE LOOP"> <h3>Nested WHILE Loop</h3> <p>The Nested WHILE Loop in SQL Server is simply a WHILE Loop written inside another WHILE Loop. When we work on multi-layered data, the Nested WHILE loops are essential. Because this concept is useful in extracting the layered data when we want to select them, it is recommended to be careful while using the nested loop.</p> <p> <strong>Syntax</strong> </p> <p>The following syntax illustrate the working of the nested WHILE Loop in SQL Server:</p> <pre> WHILE Expression BEGIN WHILE @Val2 <= 10 begin --second while loop statements sql end --this statement is outside the second --which first -- this < pre> <p>Let us explain this syntax step by step:</p> <p> <strong>Step 1:</strong> The loop starts by checking the first WHILE loop condition, and if it finds a false result, it will exit from While Loop. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution. This block will start the execution of the second WHILE loop. See step 2.</p> <p> <strong>Step 2:</strong> This step will check the condition in the Nested WHILE Loop, and if it is false, the second loop will be exit and execute the statement outside this. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution.</p> <p> <strong>Step 3:</strong> Once all the statements execute from the second WHILE loop, the control goes to the first WHILE and repeats the first step.</p> <p> <strong>Example</strong> </p> <p>The following example will print the multiplication table of 5 up to 10 using the nested WHILE loop.</p> <pre> DECLARE @val1 INT DECLARE @val2 INT SET @val1 = 5 SET @val2 = 1 WHILE @val1 <= 5 44 begin while @val2 <="10" print convert(varchar, @val1) + \' * @val2) techcodeview.com img sql-server-tutorials sql-server-while-loop-8.webp\' alt="SQL Server WHILE LOOP"> <h2>Conclusion</h2> <p>The WHILE loop is a useful method when there is a need to execute a SQL script repeatedly. The article explained how to work with the WHILE loop in MS SQL Server to execute operations such as record insertion and pagination with a simple example. Here we have also learned the BREAK and CONTINUE statements to control the WHILE loop iteration.</p> <hr></=></pre></=></pre></10></pre></=></pre></=></pre></=>
Ensuite, nous utiliserons la boucle WHILE pour insérer dix enregistrements dans cette table en exécutant le script suivant :
jsp
DECLARE @count INT; SET @count = 1; WHILE @count <= 10 begin insert into bikeshop values(\'bike-\' + cast(@count as varchar), @count*5000) set @count="@count" 1; end; < pre> <p>In this code, we have declared a variable @ <strong>count</strong> and then initialize its value with 1 using a SET clause. Next, we have to define the loop body that executes the INSERT statement to add one record in each execution. The <strong>bike_name column</strong> will append the value of a @count variable with the string <strong>Bike</strong> , and the <strong>price</strong> column determines by the value of a @count variable multiplied by <strong>5000</strong> . The loop will execute until the value of the @count variable becomes FALSE. It means the WHILE loop will execute ten times and <strong>inserts ten records</strong> into the table bikeshop.</p> <p>Now, we can verify all the records of the bikeshop table with the SELECT statement. It will display the following output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-4.webp" alt="SQL Server WHILE LOOP"> <h3>BREAK Statement</h3> <p>SQL Server also allows us to use the BREAK statement in the WHILE loop like programming languages. This statement is used to <strong>immediately stop the current iteration of the loop</strong> , and control flow resumes with the next statement after the loop. In general, we will use the <a href="/sql-server-if-else"> <strong>IF...ELSE statement</strong> </a> to check whether or not a condition has occurred.</p> <p>The following example will explain how to use the BREAK statement in the WHILE loop:</p> <pre> DECLARE @count INT; SET @count = 1; WHILE @count = 6 BEGIN BREAK END SET @Count = @Count + 1 END; </pre> <p>Executing the code will display the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-5.webp" alt="SQL Server WHILE LOOP"> <p>The value of the variable is first evaluated in this code. If it is TRUE, the control enters into the loop and prints the statement. When the variable value is greater than or equal to 6, control enters the IF...ELSE block and executes the BREAK statement to terminate the loop. If an IF...ELSE block fails to meet the condition; then, the loop will keep running until the condition is changed to FALSE.</p> <h3>CONTINUE Statement</h3> <p>SQL Server also allows us to use the CONTINUE statement in the WHILE loop like programming languages. This statement immediately <strong>terminates the current execution of the loop when the specified condition is met</strong> , and control flow returns to the beginning of the loop. In general, the IF...ELSE statement will be used to test whether or not a condition has been met.</p> <p>The CONTINUE statement in the WHILE loop is demonstrated in the following example. In this example, we'll assume that we wish to use a WHILE loop to <strong>print only odd values</strong> . The CONTINUE statement can be used to do this. This example will first <strong>test</strong> whether the variable value is <strong>odd or even</strong> . If it is even, the execution goes inside the IF…ELSE statement blocks and decrement the variable value by one. Then, it will execute the CONTINUE statement and starts a new iteration from the beginning.</p> <pre> DECLARE @Count INT SET @Count = 1 WHILE (@Count <= 1 2="0" 44 20) begin if @count % set + continue end print \'the odd value is=" + CONVERT(VARCHAR, @Count) SET @Count = @Count + 1 END </pre> <p>Executing the code snippet will display the below output:</p> <img src=" techcodeview.com img sql-server-tutorials sql-server-while-loop-6.webp\' alt="SQL Server WHILE LOOP"> <h3>How to implementing paging with WHILE loop in SQL Server?</h3> <p>We can also use the WHILE loop for implementing the paging. Paging allows displaying the subset of records from a table at any particular time. The following example will explain this concept. The WHILE loop in the code will select two records from the bikeshop table at a time. The records that have been chosen are then displayed in the output.</p> <pre> DECLARE @count INT DECLARE @limit INT; SET @count = 0 SET @limit = 2; WHILE @count <10 begin select * from bikeshop order by id offset @count rows fetch next @limit only set + 2; end; < pre> <p>Executing the code snippet will return the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-7.webp" alt="SQL Server WHILE LOOP"> <h3>Nested WHILE Loop</h3> <p>The Nested WHILE Loop in SQL Server is simply a WHILE Loop written inside another WHILE Loop. When we work on multi-layered data, the Nested WHILE loops are essential. Because this concept is useful in extracting the layered data when we want to select them, it is recommended to be careful while using the nested loop.</p> <p> <strong>Syntax</strong> </p> <p>The following syntax illustrate the working of the nested WHILE Loop in SQL Server:</p> <pre> WHILE Expression BEGIN WHILE @Val2 <= 10 begin --second while loop statements sql end --this statement is outside the second --which first -- this < pre> <p>Let us explain this syntax step by step:</p> <p> <strong>Step 1:</strong> The loop starts by checking the first WHILE loop condition, and if it finds a false result, it will exit from While Loop. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution. This block will start the execution of the second WHILE loop. See step 2.</p> <p> <strong>Step 2:</strong> This step will check the condition in the Nested WHILE Loop, and if it is false, the second loop will be exit and execute the statement outside this. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution.</p> <p> <strong>Step 3:</strong> Once all the statements execute from the second WHILE loop, the control goes to the first WHILE and repeats the first step.</p> <p> <strong>Example</strong> </p> <p>The following example will print the multiplication table of 5 up to 10 using the nested WHILE loop.</p> <pre> DECLARE @val1 INT DECLARE @val2 INT SET @val1 = 5 SET @val2 = 1 WHILE @val1 <= 5 44 begin while @val2 <="10" print convert(varchar, @val1) + \' * @val2) techcodeview.com img sql-server-tutorials sql-server-while-loop-8.webp\' alt="SQL Server WHILE LOOP"> <h2>Conclusion</h2> <p>The WHILE loop is a useful method when there is a need to execute a SQL script repeatedly. The article explained how to work with the WHILE loop in MS SQL Server to execute operations such as record insertion and pagination with a simple example. Here we have also learned the BREAK and CONTINUE statements to control the WHILE loop iteration.</p> <hr></=></pre></=></pre></10></pre></=></pre></=>
L'exécution du code affichera la sortie ci-dessous :
La valeur de la variable est d'abord évaluée dans ce code. Si c'est VRAI, le contrôle entre dans la boucle et imprime l'instruction. Lorsque la valeur de la variable est supérieure ou égale à 6, le contrôle entre dans le bloc IF...ELSE et exécute l'instruction BREAK pour terminer la boucle. Si un bloc IF...ELSE ne remplit pas la condition ; Ensuite, la boucle continuera à s'exécuter jusqu'à ce que la condition devienne FALSE.
Instruction CONTINUER
SQL Server nous permet également d'utiliser l'instruction CONTINUE dans la boucle WHILE comme les langages de programmation. Cette déclaration immédiatement termine l'exécution en cours de la boucle lorsque la condition spécifiée est remplie , et le flux de contrôle revient au début de la boucle. En général, l'instruction IF...ELSE sera utilisée pour tester si une condition a été remplie ou non.
L'instruction CONTINUE dans la boucle WHILE est illustrée dans l'exemple suivant. Dans cet exemple, nous supposerons que nous souhaitons utiliser une boucle WHILE pour imprimer uniquement les valeurs impaires . L'instruction CONTINUE peut être utilisée pour ce faire. Cet exemple va d'abord test si la valeur de la variable est impair ou pair . Si c'est pair, l'exécution va à l'intérieur des blocs d'instruction IF…ELSE et décrémente la valeur de la variable de un. Ensuite, il exécutera l'instruction CONTINUE et commencera une nouvelle itération depuis le début.
javascript le plus proche
DECLARE @Count INT SET @Count = 1 WHILE (@Count <= 1 2="0" 44 20) begin if @count % set + continue end print \'the odd value is=" + CONVERT(VARCHAR, @Count) SET @Count = @Count + 1 END </pre> <p>Executing the code snippet will display the below output:</p> <img src=" techcodeview.com img sql-server-tutorials sql-server-while-loop-6.webp\' alt="SQL Server WHILE LOOP"> <h3>How to implementing paging with WHILE loop in SQL Server?</h3> <p>We can also use the WHILE loop for implementing the paging. Paging allows displaying the subset of records from a table at any particular time. The following example will explain this concept. The WHILE loop in the code will select two records from the bikeshop table at a time. The records that have been chosen are then displayed in the output.</p> <pre> DECLARE @count INT DECLARE @limit INT; SET @count = 0 SET @limit = 2; WHILE @count <10 begin select * from bikeshop order by id offset @count rows fetch next @limit only set + 2; end; < pre> <p>Executing the code snippet will return the below output:</p> <img src="//techcodeview.com/img/sql-server-tutorials/44/sql-server-while-loop-7.webp" alt="SQL Server WHILE LOOP"> <h3>Nested WHILE Loop</h3> <p>The Nested WHILE Loop in SQL Server is simply a WHILE Loop written inside another WHILE Loop. When we work on multi-layered data, the Nested WHILE loops are essential. Because this concept is useful in extracting the layered data when we want to select them, it is recommended to be careful while using the nested loop.</p> <p> <strong>Syntax</strong> </p> <p>The following syntax illustrate the working of the nested WHILE Loop in SQL Server:</p> <pre> WHILE Expression BEGIN WHILE @Val2 <= 10 begin --second while loop statements sql end --this statement is outside the second --which first -- this < pre> <p>Let us explain this syntax step by step:</p> <p> <strong>Step 1:</strong> The loop starts by checking the first WHILE loop condition, and if it finds a false result, it will exit from While Loop. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution. This block will start the execution of the second WHILE loop. See step 2.</p> <p> <strong>Step 2:</strong> This step will check the condition in the Nested WHILE Loop, and if it is false, the second loop will be exit and execute the statement outside this. Otherwise, if the result is true, the control goes inside the BEGIN and END block for further execution.</p> <p> <strong>Step 3:</strong> Once all the statements execute from the second WHILE loop, the control goes to the first WHILE and repeats the first step.</p> <p> <strong>Example</strong> </p> <p>The following example will print the multiplication table of 5 up to 10 using the nested WHILE loop.</p> <pre> DECLARE @val1 INT DECLARE @val2 INT SET @val1 = 5 SET @val2 = 1 WHILE @val1 <= 5 44 begin while @val2 <="10" print convert(varchar, @val1) + \' * @val2) techcodeview.com img sql-server-tutorials sql-server-while-loop-8.webp\' alt="SQL Server WHILE LOOP"> <h2>Conclusion</h2> <p>The WHILE loop is a useful method when there is a need to execute a SQL script repeatedly. The article explained how to work with the WHILE loop in MS SQL Server to execute operations such as record insertion and pagination with a simple example. Here we have also learned the BREAK and CONTINUE statements to control the WHILE loop iteration.</p> <hr></=></pre></=></pre></10></pre></=>