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

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server BREAK<\/code> statement to immediately exit a WHILE<\/code> loop.<\/p>\n\n\n\n

SQL Server BREAK statement overview #<\/a><\/h2>\n\n\n\n

In the previous tutorial, you have learned how to use the WHILE<\/a><\/code> statement to create a loop. To exit the current iteration of a loop, you use the BREAK<\/code> statement.<\/p>\n\n\n\n

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

WHILE Boolean_expression\nBEGIN<\/span>\n    -- statements<\/span>\n   IF<\/span> condition\n        BREAK;\n    -- other statements    <\/span>\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Suppose we have a WHILE<\/code> loop nested inside another WHILE<\/code> loop:<\/p>\n\n\n
WHILE Boolean_expression1\nBEGIN<\/span>\n    -- statement<\/span>\n    WHILE<\/span> Boolean_expression2\n    BEGIN<\/span>\n        IF<\/span> condition\n            BREAK;\n    END<\/span>\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the BREAK<\/code> statement only exits the innermost loop in the WHILE<\/code> statement.<\/p>\n\n\n\n
Note that the BREAK<\/code> statement can be used only inside the WHILE<\/code> loop. The IF<\/code> statement is often used with the BREAK<\/code> statement but it is not required.<\/p>\n\n\n\n
SQL Server BREAK statement example #<\/a><\/h2>\n\n\n\n
The following example illustrates how to use the BREAK<\/code> statement:<\/p>\n\n\n
DECLARE<\/span> @counter INT<\/span> = 0<\/span>;\n\nWHILE @counter <= 5\nBEGIN<\/span>\n    SET<\/span> @counter = @counter + 1<\/span>;\n    IF @counter = 4\n        BREAK;\n    PRINT @counter;\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
1\n2\n3<\/code><\/span><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, we declared a variable<\/a> named @counter<\/code> and set its value to zero.<\/p>\n\n\n\n
Then, we used the WHILE<\/code> statement to increases the @counter<\/code> by one in each iteration and print out the @counter<\/code>‘s value as long as the value of the @counter<\/code> is less than or equal to five.<\/p>\n\n\n\n
Inside the loop, we also checked if the value of @counter<\/code> equals four, then we exited the loop. In the fourth iteration, the value of the counter reached 4, then the loop is terminated. Also, the PRINT<\/code> statement after the BREAK<\/code> statement was skipped.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server BREAK<\/code> statement to exit a loop immediately.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
In this syntax, the BREAK<\/code> statement exit the WHILE<\/code> loop immediately once the condition<\/code>  specified in the IF<\/a><\/code> statement is met. All the statements between the BREAK<\/code> and END<\/code> keywords are skipped.<\/p>\n\n\n\n