{"id":824,"date":"2018-10-03T14:03:07","date_gmt":"2018-10-03T07:03:07","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=824"},"modified":"2020-04-11T20:13:13","modified_gmt":"2020-04-11T13:13:13","slug":"sql-server-while","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-while\/","title":{"rendered":"SQL Server WHILE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server WHILE<\/code> statement to execute a statement block repeatedly based on a specified condition.<\/p>\n\n\n\n

Overview of WHILE statement #<\/a><\/h2>\n\n\n\n

The WHILE<\/code> statement is a control-flow statement that allows you to execute a statement block repeatedly as long as a specified condition is TRUE<\/code>.<\/p>\n\n\n\n

The following illustrates the syntax of the WHILE<\/code> statement:<\/p>\n\n\n

WHILE Boolean_expression   \n     { sql_statement | statement_block}  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, the Boolean_expression<\/code> is an expression that evaluates to TRUE<\/code> or FALSE<\/code>.<\/p>\n\n\n\n
If the Boolean_expression<\/code> evaluates to FALSE<\/code> when entering the loop, no statement inside the WHILE<\/code> loop will be executed.<\/p>\n\n\n\n
Inside the WHILE<\/code> loop, you must change some variables<\/a> to make the Boolean_expression<\/code> returns FALSE<\/code> at some points. Otherwise, you will have an indefinite loop.<\/p>\n\n\n\n
Note that if the Boolean_expression<\/code> contains a SELECT<\/a><\/code> statement, it must be enclosed in parentheses.<\/p>\n\n\n\n
To exit the current iteration of the loop immediately, you use the BREAK<\/a><\/code> statement. To skip the current iteration of the loop and start the new one, you use the CONTINUE<\/a><\/code> statement.<\/p>\n\n\n\n
SQL Server WHILE example #<\/a><\/h2>\n\n\n\n
Let’s take an example of using the SQL Server WHILE<\/code> statement to understand it better.<\/p>\n\n\n\n
The following example illustrates how to use the WHILE<\/code> statement to print out numbers from 1 to 5:<\/p>\n\n\n
DECLARE<\/span> @counter INT<\/span> = 1<\/span>;\n\nWHILE @counter <= 5\nBEGIN<\/span>\n    PRINT @counter;\n    SET<\/span> @counter = @counter + 1<\/span>;\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
1\n2\n3\n4\n5<\/code><\/pre>\n\n\n\n
In this example:<\/p>\n\n\n\n
Second, sql_statement | statement_block<\/code> is any Transact-SQL statement or a set of Transact-SQL statements. A statement block is defined using the BEGIN...END<\/a><\/code> statement.<\/p>\n\n\n\n