{"id":3288,"date":"2015-06-21T16:25:47","date_gmt":"2015-06-21T10:55:47","guid":{"rendered":"http:\/\/sqlhints.com\/?p=3288"},"modified":"2015-06-27T20:48:41","modified_gmt":"2015-06-27T15:18:41","slug":"while-loop-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/06\/21\/while-loop-in-sql-server\/","title":{"rendered":"WHILE loop in Sql Server"},"content":{"rendered":"<p style=\"text-align: justify;\"><strong>WHILE loop<\/strong> is the looping construct supported by Sql Server. Sql server doesn&#8217;t have <em>for&#8230;loop<\/em>, <em>do&#8230;while loop<\/em> etc, but with WHILE loop we can simulate these missing looping constructs behaviour.<\/p>\n<p style=\"text-align: justify;\">This article will cover the following:<\/p>\n<ul>\n<li style=\"text-align: justify;\"><strong>Introduction to WHILE loop<\/strong><\/li>\n<li style=\"text-align: justify;\"><strong>BREAK Statement<\/strong><\/li>\n<li style=\"text-align: justify;\"><strong>CONTINUE Statement<\/strong><\/li>\n<li style=\"text-align: justify;\"><strong>DO WHILE loop<\/strong><\/li>\n<li style=\"text-align: justify;\"><strong><a href=\"https:\/\/sqlhints.com\/2015\/06\/21\/looping-through-table-records-in-sql-server\/\" title=\"Looping through table records in Sql Server\" target=\"_blank\">Looping through table records<\/a><\/strong><\/li>\n<\/ul>\n<h3>WHILE LOOP<\/h3>\n<p style=\"text-align: justify;\">A while loop will check the condition first and then executes the block of Sql Statements within it as along as the condition evaluates to true.<br \/>\n<em><strong>Syntax:<br \/>\n<\/strong><\/em> WHILE <strong><em>Condition<\/em><\/strong><br \/>\nBEGIN<br \/>\n<strong><em>Sql Statements<\/em><\/strong><br \/>\nEND<\/p>\n<p style=\"text-align: justify;\"><strong>Example:<\/strong> Basic while loop example. The below while loop executes the statements within it 4 times.<\/p>\n<pre class=\"brush: sql; gutter: false\">DECLARE @LoopCounter INT = 1\r\nWHILE ( @LoopCounter &lt;= 4)\r\nBEGIN\r\n\tPRINT @LoopCounter  \r\n\tSET @LoopCounter  = @LoopCounter  + 1\r\nEND\t\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n1<br \/>\n2<br \/>\n3<br \/>\n4<\/p>\n<h3>BREAK Statement<\/h3>\n<p style=\"text-align: justify;\">If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop.<\/p>\n<p style=\"text-align: justify;\"><strong>Example: WHILE loop with BREAK statement<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">DECLARE @LoopCounter INT = 1\r\nWHILE ( @LoopCounter &lt;= 4)\r\nBEGIN\r\n\tPRINT @LoopCounter  \r\n\tIF(@LoopCounter = 2)\r\n\t\tBREAK\r\n \tSET @LoopCounter  = @LoopCounter  + 1\r\nEND\r\nPRINT &#039;Statement after while loop&#039;<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Break-Statement-Sql-Server.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3289\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Break-Statement-Sql-Server.jpg\" alt=\"WHILE Loop Break Statement Sql Server\" width=\"330\" height=\"248\" srcset=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Break-Statement-Sql-Server.jpg 330w, https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Break-Statement-Sql-Server-300x225.jpg 300w\" sizes=\"auto, (max-width: 330px) 100vw, 330px\" \/><\/a><\/p>\n<h3>CONTINUE Statement<\/h3>\n<p style=\"text-align: justify;\">If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration.<\/p>\n<p><strong>Example: WHILE loop with CONTINUE statement<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">DECLARE @LoopCounter INT = 0\r\n\tWHILE ( @LoopCounter &lt;= 3)\r\n\tBEGIN\r\n\t\tSET @LoopCounter  = @LoopCounter  + 1 \r\n\r\n\t\tIF(@LoopCounter = 2)\r\n\t\t  CONTINUE\r\n \r\n\t\tPRINT @LoopCounter \r\n\tEND\r\n\tPRINT &#039;Statement after while loop&#039;<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Continue-Statement-Sql-Server.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3290\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Continue-Statement-Sql-Server.jpg\" alt=\"WHILE Loop Continue Statement Sql Server\" width=\"327\" height=\"286\" srcset=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Continue-Statement-Sql-Server.jpg 327w, https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/WHILE-Loop-Continue-Statement-Sql-Server-300x262.jpg 300w\" sizes=\"auto, (max-width: 327px) 100vw, 327px\" \/><\/a><\/p>\n<h3>DO&#8230;WHILE Loop in Sql Server<\/h3>\n<p style=\"text-align: justify;\">Sql Server doesn&#8217;t have a DO&#8230;WHILE loop construct, but same behaviour can be achieved using a WHILE loop as shown by the below example.<\/p>\n<pre class=\"brush: sql; gutter: false\">DECLARE @LoopCounter INT = 1\r\nWHILE(1=1)\r\nBEGIN\r\n   PRINT @LoopCounter\r\n   SET @LoopCounter  = @LoopCounter  + 1 \t\r\n   IF(@LoopCounter &gt; 4)\r\n\tBREAK;\t\t   \r\nEND<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/DO-WHILE-Loop-in-Sql-Server.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3291\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/DO-WHILE-Loop-in-Sql-Server.jpg\" alt=\"DO WHILE Loop in Sql Server\" width=\"321\" height=\"230\" srcset=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/DO-WHILE-Loop-in-Sql-Server.jpg 321w, https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/06\/DO-WHILE-Loop-in-Sql-Server-300x215.jpg 300w\" sizes=\"auto, (max-width: 321px) 100vw, 321px\" \/><\/a><\/p>\n<h3>Looping through table records one row at a time<\/h3>\n<p style=\"text-align: justify;\">Article <a href=\"https:\/\/sqlhints.com\/2015\/06\/21\/looping-through-table-records-in-sql-server\/\" title=\"Looping through table records in Sql Server\" target=\"_blank\">Looping through table records in Sql Server<\/a> lists out the various approaches of looping through table records row by row.<\/p>\n<p><strong>[ALSO READ] <\/strong><a href=\"https:\/\/sqlhints.com\/2013\/11\/05\/printselect-statement-messages-within-while-loop-or-batch-of-statement-is-not-displayed-immediately-after-its-execution-sql-server\/\" title=\"PRINT\/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it\u2019s execution- Sql Server\" target=\"_blank\">PRINT\/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it\u2019s execution- Sql Server<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>WHILE loop is the looping construct supported by Sql Server. Sql server doesn&#8217;t have for&#8230;loop, do&#8230;while loop etc, but with WHILE loop we can simulate these missing looping constructs behaviour. This article will cover the following: Introduction to WHILE loop BREAK Statement CONTINUE Statement DO WHILE loop Looping through table records WHILE LOOP A while &hellip; <a href=\"https:\/\/sqlhints.com\/2015\/06\/21\/while-loop-in-sql-server\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">WHILE loop in Sql Server<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[507,506,511,510,542,519,515,543,505,321,986,502,508,509,503,504],"class_list":["post-3288","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-break","tag-continue","tag-do-loop","tag-do-while-loop","tag-do-while-loop-in-sql-server","tag-for-loop","tag-loop","tag-loop-in-sql-server","tag-looping-construct-in-sql","tag-sql","tag-sql-server","tag-while","tag-while-break-continue","tag-while-continue","tag-while-loop","tag-while-loop-in-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-R2","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3288","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=3288"}],"version-history":[{"count":22,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3288\/revisions"}],"predecessor-version":[{"id":3381,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3288\/revisions\/3381"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=3288"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=3288"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=3288"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}