BREAK of Loop Statement in SQL Server Transact-SQL
How to break a WHILE look statement in SQL Server Transact-SQL? How to use BREAK statements?
You can use the BREAK statement to break a WHILE loop inside the statement block in Transact-SQL using this syntax:
WHILE condition BEGIN statement_1 statement_2 ... BREAK; statement_n ... END
When a BREAK statement inside a WHILE loop is executed, the system will:
Here is an example on how to use BREAK statements inside WHILE loops:
DECLARE @count INT = 1, @sum INT = 0; WHILE 1=1 BEGIN SET @sum = @sum + @count; SET @count = @count+1; IF @count > 10 BREAK; END PRINT 'Sum of 1 to 10: ' + CAST(@sum AS CHAR);
Here is another example on how to use a BREAK statement to stop a WHILE loop early:
-- Counting number of days in 2000 DECLARE @date DATETIME; DECLARE @count INT; SET @date = '2000-01-01'; SET @count = 0; WHILE 1=1 BEGIN IF DATEPART(YEAR, @date) > 2000 BREAK; SET @count = @count + 1; SET @date = DATEADD(DAY, 1, @date); END SELECT @count; 366 -- 2000 is a leap year!
2017-01-11, 434👍, 0💬
How to run Queries with "sqlcmd" tool in SQL Server? "sqlcmd" is a client tool that you can use to i...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
How To Recreate an Existing Index in SQL Server? If you want to change the definition of an existing...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...