{"id":3045,"date":"2020-08-04T04:24:04","date_gmt":"2020-08-04T04:24:04","guid":{"rendered":"https:\/\/studysection.com\/blog\/?p=3045"},"modified":"2021-01-20T04:48:10","modified_gmt":"2021-01-20T04:48:10","slug":"mysql-stored-procedure-while-loop","status":"publish","type":"post","link":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/","title":{"rendered":"MySQL Stored Procedure while loop"},"content":{"rendered":"

Introduction<\/h2>\n

The While loop is a loop statement that performs a block of code repeatedly as long as a condition is true. If we want to execute a block of statements(SQL Code<\/a>) repeatedly based on a condition, While loop<\/strong> is the one supported by MySQL. <\/p>\n

While loop Syntax<\/strong>
\nWHILE (expression)
\n\tDO
\n\t\tstatements
\nEND WHILE<\/p>\n

As we know, the expression is checked by the while loop at the starting of every iteration. If the expression evaluates to right, MySQL performs statements between WHILE and END WHILE till the expression evaluates to false.<\/p>\n

Example of WHILE loop with stored procedure<\/h3>\n


\nDELIMITER $$
\nDROP PROCEDURE IF EXISTS mySQLTestLoop $$
\nCREATE PROCEDURE mySQLTestLoop()
\nBEGIN
\n SET @counter = 10;
\n WHILE (@endPosition > 0)
\n DO
\n PRINT @counter;
\n SET @counter = @counter - 1;
\n END WHILE;
\nEND $$
\nDELIMITER ;
\nCALL mySQLTestLoop();
\nDROP PROCEDURE mySQLTestLoop;
\n<\/code><\/p>\n

The following steps are used in the above example:<\/p>\n

    \n
  1. Start delimiter to tell the MySQL client to treat the statements as an entire statement.<\/li>\n
  2. Drop the procedure by using Drop with IF EXIST condition (so that MySQL doesn’t give an error if not exist).<\/li>\n
  3. Use BEGIN to execute compound statements.<\/li>\n
  4. Initialize the counter to 10 using a SET data type.<\/li>\n
  5. Check while loop condition. It will be true if greater than 0.<\/li>\n
  6. Put statements after ‘DO’, if you want to execute.<\/li>\n
  7. Print counter value and decrement it by 1.<\/li>\n
  8. END while and Delimiter.<\/li>\n
  9. Call procedure to start execution.<\/li>\n
  10. Drop procedure after execution.<\/li>\n<\/ol>\n

    If you have skills in PHP programming and you want to enhance your career in this field, a PHP certification from StudySection can help you reach your desired goals. Both beginner level and expert level PHP certification exams<\/a> are offered by StudySection along with other programming certification exams. <\/em><\/small><\/p>\n","protected":false},"excerpt":{"rendered":"

    Introduction The While loop is a loop statement that performs a block of code repeatedly as long as a condition<\/p>\n","protected":false},"author":1,"featured_media":3047,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[507,506],"acf":[],"yoast_head":"\nStudySection Blog - MySQL Stored Procedure while loop<\/title>\n<meta name=\"description\" content=\"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"StudySection Blog - MySQL Stored Procedure while loop\" \/>\n<meta property=\"og:description\" content=\"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog Posts on famous people, innovations and educational topics\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/studysection\" \/>\n<meta property=\"article:published_time\" content=\"2020-08-04T04:24:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-20T04:48:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2020\/08\/mysql.png\" \/>\n\t<meta property=\"og:image:width\" content=\"300\" \/>\n\t<meta property=\"og:image:height\" content=\"200\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin-studysection-blog\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@studysection\" \/>\n<meta name=\"twitter:site\" content=\"@studysection\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin-studysection-blog\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\"},\"author\":{\"name\":\"admin-studysection-blog\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\"},\"headline\":\"MySQL Stored Procedure while loop\",\"datePublished\":\"2020-08-04T04:24:04+00:00\",\"dateModified\":\"2021-01-20T04:48:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\"},\"wordCount\":251,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"keywords\":[\"MySQL\",\"Procedure\"],\"articleSection\":[\"Learn and Grow\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\",\"url\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\",\"name\":\"StudySection Blog - MySQL Stored Procedure while loop\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/#website\"},\"datePublished\":\"2020-08-04T04:24:04+00:00\",\"dateModified\":\"2021-01-20T04:48:10+00:00\",\"description\":\"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/studysection.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Stored Procedure while loop\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/studysection.com\/blog\/#website\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"name\":\"Blog Posts on famous people, innovations and educational topics\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/studysection.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/studysection.com\/blog\/#organization\",\"name\":\"StudySection\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"contentUrl\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"width\":920,\"height\":440,\"caption\":\"StudySection\"},\"image\":{\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/studysection\",\"https:\/\/twitter.com\/studysection\",\"https:\/\/www.instagram.com\/study.section\/\",\"https:\/\/www.linkedin.com\/company\/studysection\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\",\"name\":\"admin-studysection-blog\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"caption\":\"admin-studysection-blog\"},\"url\":\"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"StudySection Blog - MySQL Stored Procedure while loop","description":"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/","og_locale":"en_US","og_type":"article","og_title":"StudySection Blog - MySQL Stored Procedure while loop","og_description":"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.","og_url":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/","og_site_name":"Blog Posts on famous people, innovations and educational topics","article_publisher":"https:\/\/www.facebook.com\/studysection","article_published_time":"2020-08-04T04:24:04+00:00","article_modified_time":"2021-01-20T04:48:10+00:00","og_image":[{"width":300,"height":200,"url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2020\/08\/mysql.png","type":"image\/png"}],"author":"admin-studysection-blog","twitter_card":"summary_large_image","twitter_creator":"@studysection","twitter_site":"@studysection","twitter_misc":{"Written by":"admin-studysection-blog","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#article","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/"},"author":{"name":"admin-studysection-blog","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402"},"headline":"MySQL Stored Procedure while loop","datePublished":"2020-08-04T04:24:04+00:00","dateModified":"2021-01-20T04:48:10+00:00","mainEntityOfPage":{"@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/"},"wordCount":251,"commentCount":0,"publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"keywords":["MySQL","Procedure"],"articleSection":["Learn and Grow"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/","url":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/","name":"StudySection Blog - MySQL Stored Procedure while loop","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/#website"},"datePublished":"2020-08-04T04:24:04+00:00","dateModified":"2021-01-20T04:48:10+00:00","description":"If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.","breadcrumb":{"@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/studysection.com\/blog\/mysql-stored-procedure-while-loop\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/studysection.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL Stored Procedure while loop"}]},{"@type":"WebSite","@id":"https:\/\/studysection.com\/blog\/#website","url":"https:\/\/studysection.com\/blog\/","name":"Blog Posts on famous people, innovations and educational topics","description":"","publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/studysection.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/studysection.com\/blog\/#organization","name":"StudySection","url":"https:\/\/studysection.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","contentUrl":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","width":920,"height":440,"caption":"StudySection"},"image":{"@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/studysection","https:\/\/twitter.com\/studysection","https:\/\/www.instagram.com\/study.section\/","https:\/\/www.linkedin.com\/company\/studysection"]},{"@type":"Person","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402","name":"admin-studysection-blog","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","caption":"admin-studysection-blog"},"url":"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/"}]}},"views":1619,"_links":{"self":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/3045"}],"collection":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/comments?post=3045"}],"version-history":[{"count":4,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/3045\/revisions"}],"predecessor-version":[{"id":3822,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/3045\/revisions\/3822"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media\/3047"}],"wp:attachment":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media?parent=3045"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/categories?post=3045"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/tags?post=3045"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}