Hello friends, today in this article we are going to learn While, Break and Continue in Sql Server.
Like in other programming languages while statement is use for looping in sql server. It has one point for enter in loop with its initial value and condition and with in loop a condition to control the loop with incremental and decremental operation moreover break and continue statements.
The statements with in the loop are executed repeatedly as long as the specified condition is true.
Syntax:
WHILE Boolean_expression { sql_statement | statement_block | BREAK | CONTINUE
Boolean_expression: It is an conditional expression that returns TRUE or FALSE.
{sql_statement | statement_block} : T-Sql (Transact-SQL) statement or statements with a block. The block, use the control-of-flow keywords BEGIN and END.
Example:
Lets print a number from 1 to 10
DECLARE @cnt int = 1
WHILE @cnt < 10
BEGIN
PRINT @cnt
SET @cnt+=1 --OR SET @cnt = @cnt+1
END
Go
Lets print a number from 10 to 1
DECLARE @cnt int = 10
WHILE @cnt >= 1
BEGIN
PRINT @cnt
SET @cnt-=1 --OR SET @cnt = @cnt-1
END
Go
BREAK: BREAK is used with while loop to exit from the current WHILE loop with an condition. it is used with if statement.
If the current WHILE loop is nested inside another while loop, BREAK exits only the current loop, and control is given to the next statement in the outer loop.
Example:
Following sql exit the current loop when @count will become equal to or greater than 5. Therefore loop will print counting from 1 to 5.
DECLARE @cnt int = 1
WHILE @cnt < 10
BEGIN
PRINT @cnt
IF @cnt >=5
break;
SET @cnt+=1
END
Go
OUTPUT: 1 2 3 4 5
Example:
DECLARE @c int = 1
DECLARE @cnt int = 1
WHILE @c<=5
BEGIN
SET @cnt = 1
WHILE @cnt < 10
BEGIN
PRINT @cnt
IF @cnt >=5
break;
SET @cnt+=1
END
SET @c+=1
END
Go
OUTPUT: 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 Outer loop 1 prints inner loop 1 2 3 4 5 Outer loop 2 prints inner loop 1 2 3 4 5 Outer loop 3 prints inner loop 1 2 3 4 5 Outer loop 4 prints inner loop 1 2 3 4 5 Outer loop 5 prints inner loop 1 2 3 4 5
CONTINUE: Continue statement is use for restart a WHILE loop. with some condition. it also use with if statement. Any statements after the CONTINUE keyword are ignored.
Example:
Following code will print counts from 1 to 5. When @cnt reach greater than 5, the control go back to loop entering point and will left remaining code execution.
DECLARE @cnt int = 0
WHILE @cnt < 10
BEGIN
SET @cnt+=1
IF @cnt>5
continue;
PRINT @cnt
END
Go
OUTPUT: 1 2 3 4 5
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.